Copy specific columns vba

[owen4512]

Hi all,

The below code works however it requires some adapting to meet my needs. Currently the below will copy data on sheet3 in range A:N and paste this to sheet5 range B9. The difficulty i'm having is that i don't want to copy all the data from A:N but instead i want to copy data in B,C,E,H,I and paste into sheet5 B9

How can i adapt the below? Any help would be appreciated

Sub Create_Report()

    Dim i, last_row_Data As Integer
    Dim Records As Long

    Records = Sheet1.Range("N15").Value
    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    
    For i = 2 To last_row_Data
        If Sheet2.Range("M" & i).Value = "Outstanding" Then
            Records = Records + 1
            Sheet2.Activate
            Sheet2.Range(Cells(i, 1), Cells(i, 14)).Copy
            Sheet5.Activate
            Sheet5.Range("B9").PasteSpecial xlPasteValuesAndNumberFormats
        End If
     Next i
    
End Sub

 

[Joe4]

Here is one way.

Replace this line:

           Sheet2.Range(Cells(i, 1), Cells(i, 14)).Copy

with this:

           rng = "B" & i & ", C" & i & ", E" & i & ", H" & i & ", I" & i
           Sheet2.Range(rng).Copy

 

[owen4512]

Here is one way.

Replace this line:

           Sheet2.Range(Cells(i, 1), Cells(i, 14)).Copy

with this:

           rng = "B" & i & ", C" & i & ", E" & i & ", H" & i & ", I" & i
           Sheet2.Range(rng).Copy

Thank you, Joe - That's done the trick!

 

[Joe4]

You are welcome.
Glad I was able to help!