Search code examples
excelfor-loopcopyworksheetvba

How to conditionally copy the data rows from one Excel Worksheet to another

I have a list of projects and project details in my "Project Master" Excel Worksheet placed in 4 columns: project type, project numbers, project value, and project managers' names. I want to write a macro to copy the content of these 4 columns from the "Project Master" Worksheet to another Worksheet ("Details") in the same Workbook but only if the row contains the project type "A". Is this possible? Regards, CK

Solution

  • Pertinent to your task description, the sample Worksheet content may look like shown in the following table:

    Type    Num Value   Manager Name
B       3   3.14    I. Newton
A       5   2.71    T. Edison
C       8   9.95    H. Ford
A       1   4.99    S. Jobs
D       4   21      G. Leibniz

    and the corresponding sample VBA Sub CopyDetails() to perform the task is shown below:

    Sub CopyDetails()
  Dim ws As Worksheet
  Dim LastRow As Long

  Set ws = ThisWorkbook.Worksheets("Project Master")
  LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  For i = 2 To LastRow
    If (ws.Range("A" & i) = "A") Then
        ws.Range("B" & i & ":D" & i).Copy Destination:=Worksheets("Details").Range("A" & i)
    End If
  Next i
End Sub

    Alternatively, you may set the reference to Excel Worksheet objects like for e.g.:

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Hope this may help.