Copy specific columns from multiple sheets to another sheet

[Gogleguy]

Hey Guys,
I have multiple sheets with the same headers (Column A to Column K) and dynamic rows. The data in each sheet is already sorted. I want to copy data from only Column A, B, J, K from each sheet till last row where value of J is greater than 10 and paste to single destination sheet from Column U onwards as there is already data in the destination sheet. I also need the headers to be pasted once at the top. Please help.

 

[Joe4]

In cases like this, it is often very helpful for us to see an example, as small sample of the data you are working, and to see your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Gogleguy]

In cases like this, it is often very helpful for us to see an example, as small sample of the data you are working, and to see your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you Joe4.

Here are headers which are same in 3 sheets - Sheet1, Sheet2, Sheet3. I want to copy Column A, B, J, K to another sheet only where Column J has value more than 10

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I	Column J	Column K
Supervisor Email	Supervisor First Name	User Preferred Language	User Full Name	User First Name	User Email Address	Task Type	Task Status	User Overdue Count	Supervisor Overdue Count	Supervisor Preferred Language

 

[Joe4]

I was hoping for some actual data, so I could see how everything is structured and what it looks like.
The devil is often in the minor details that are left out!

What is the name of the sheet you will be copying it to?
You said you will be copying it to column U, does that mean columns A, B, J, and K will be copied to columns U, V, W, and X?
I am also a bit confused by the line that "there is already data in the destination" sheet, but you need the headers copied in once?
So where exactly does this other data exist?
(This is where it would be real helpful just to see what it looks like and what you want the expected output to look like, instead of having to ask a bunch of questions).

 

[Gogleguy]

What is the name of the sheet you will be copying it to?
You said you will be copying it to column U, does that mean columns A, B, J, and K will be copied to columns U, V, W, and X?

The Sheet Name I am copying to is "Stats". Yes. It needs to copy to U, V, W, X

 

[Joe4]

The Sheet Name I am copying to is "Stats". Yes. It needs to copy to U, V, W, X

I was editing my reply as you were replying, so you probably didn't see my last question.

I am also a bit confused by the line that "there is already data in the destination" sheet, but you need the headers copied in once?
So where exactly does this other data exist?

 

[Joe4]

Caveat: This isn't the most efficient method, as it uses loops, but it should work. If I had more time, I would look for solutions with less looping.

Sub MyCopyData()

    Dim lr As Long
    Dim r As Long
    Dim s As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Set destination sheet
    Set ws2 = Sheets("Stats")
    
'   Set initial row on destination sheet
    nr = 2
    
'   Loop through Sheet1, Sheet2, and Sheet3
    For s = 1 To 3
'       Set worksheet object
        Set ws1 = Sheets("Sheet" & s)
'       If first sheet, copy headers
        If s = 1 Then
'           Copy headings from Sheet1 to Stats Sheet
            ws1.Range("A1:B1").Copy ws2.Range("U1")
            ws1.Range("J1:K1").Copy ws2.Range("W1")
        End If
'       Find last row in column J with data
        lr = ws1.Cells(ws1.Rows.Count, "J").End(xlUp).Row
'       Loop through all rows in column J
        For r = 2 To lr
'           Check value in column J
            If ws1.Cells(r, "J") > 10 Then
'               Copy values over to destination sheet
                ws2.Cells(nr, "U").Value = ws1.Cells(r, "A")
                ws2.Cells(nr, "V").Value = ws1.Cells(r, "B")
                ws2.Cells(nr, "W").Value = ws1.Cells(r, "J")
                ws2.Cells(nr, "X").Value = ws1.Cells(r, "K")
'               Increment next row counter
                nr = nr + 1
            End If
        Next r
    Next s
        
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[Gogleguy]

Caveat: This isn't the most efficient method, as it uses loops, but it should work. If I had more time, I would look for solutions with less looping.

Sub MyCopyData()

    Dim lr As Long
    Dim r As Long
    Dim s As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim nr As Long
   
    Application.ScreenUpdating = False
   
'   Set destination sheet
    Set ws2 = Sheets("Stats")
   
'   Set initial row on destination sheet
    nr = 2
   
'   Loop through Sheet1, Sheet2, and Sheet3
    For s = 1 To 3
'       Set worksheet object
        Set ws1 = Sheets("Sheet" & s)
'       If first sheet, copy headers
        If s = 1 Then
'           Copy headings from Sheet1 to Stats Sheet
            ws1.Range("A1:B1").Copy ws2.Range("U1")
            ws1.Range("J1:K1").Copy ws2.Range("W1")
        End If
'       Find last row in column J with data
        lr = ws1.Cells(ws1.Rows.Count, "J").End(xlUp).Row
'       Loop through all rows in column J
        For r = 2 To lr
'           Check value in column J
            If ws1.Cells(r, "J") > 10 Then
'               Copy values over to destination sheet
                ws2.Cells(nr, "U").Value = ws1.Cells(r, "A")
                ws2.Cells(nr, "V").Value = ws1.Cells(r, "B")
                ws2.Cells(nr, "W").Value = ws1.Cells(r, "J")
                ws2.Cells(nr, "X").Value = ws1.Cells(r, "K")
'               Increment next row counter
                nr = nr + 1
            End If
        Next r
    Next s
       
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub

Thank you so much Joe4. This is working for the first loop. I will figure out how to make it do the 2nd and 3rd sheet. Thanks a ton.

 

[Joe4]

Thank you so much Joe4. This is working for the first loop. I will figure out how to make it do the 2nd and 3rd sheet. Thanks a ton.

This section of the code ensures that it will run against "Sheet1", "Sheet2", and "Sheet3".

    For s = 1 To 3
'       Set worksheet object
        Set ws1 = Sheets("Sheet" & s)

If it does not run against "Sheet2" and "Sheet3" then the most likely issue is:
- Your other two sheets are not really named "Sheet2" and "Sheet3"
- The other two sheets are not structured the same way as "Sheet1"
- No values in column J are greater than 10, or the numbers are entered as text and not numbers