Thread: Copy data from multiple worksheets & paste into one worksheet.

Hi,

This is my first time on here so I hope someone is able to help me.
I have a workbook with multiple worksheets. I need to copy data from the first 11 columns of 16 of the worksheets & paste the values in a 'loading' worksheet. The worksheets that I need the data from have a similarity in that they are named '**** Pre Load'. Column 6 of the 11 columns is empty but I need the blank column in the loading file. When pasted into the loading file I need it so that the first sheets data will be pasted in cell A2:K10, for example, the next sheet will be from A11:K25 etc.

If anyone can help I would be extremely greatful.

Thanks,
Rod

Would you need the filename to be inserted from where the data has been copied?

Use this code in a blank module of your file. Press Alt + F11. It will open a blank code window. On the left hand side, you will find Microsoft Excel Objects, right click and select Insert-> Module. Copy this code into the module window on the right.

Option Explicit
Dim i As Long
Dim lrow As Long

Sub copy_data()

'Copies header
For i = 1 To Worksheets.Count
    If Worksheets(i).Name Like "*Pre Load" And Worksheets(i).Name <> "Loading" Then
        Worksheets(i).Rows("1:1").Copy Worksheets("Loading").Range("A1")
        Exit For
    End If
Next i

'Copies data
For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name Like "*Pre Load" And .Name <> "Loading" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A2:K" & lrow).Copy Worksheets("Loading").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
Next i
End Sub

Come back to your sheet. Go to Tools -> Macro-> Macros and run this macro.

Yes please attach. Since it was more of a generic question, i just framed a solution. You will need to change it to match your sheet names.
I guess, your "loading" sheet starts with "l" (small l") while the code asks for a capital L. You can try changing that and checking again.

If you still face a problem, please attach a file.

Your tabs - 1 preload, 2 preload, etc do not have headers. Please insert them so i know which columns need to be copied.

Should the data be copied one below the other?

By headers i mean the first line which contains the name for the column. If you do not have headers, can you tell me which columns need to be copied?

Use this code.

Option Explicit
Dim i As Long
Dim lrow As Long
Dim lastrow As Long

Sub copy_data()

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name Like "*Pre Load" And .Name <> "Loading File" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            lastrow = Worksheets("Loading File").Range("A" & Rows.Count).End(xlUp).Row
            .Range("A1:F" & lrow).Copy Worksheets("Loading File").Range("A" & lastrow + 1)
            .Range("H1:K" & lrow).Copy Worksheets("Loading File").Range("H" & lastrow + 1)
        End If
    End With
Next i

Application.ScreenUpdating = True

End Sub

For paste special, change the code from

 .Range("A1:F" & lrow).Copy Worksheets("Loading File").Range("A" & lastrow + 1)
            .Range("H1:K" & lrow).Copy Worksheets("Loading File").Range("H" & lastrow + 1)

To

.Range("A1:F" & lrow).Copy 
Worksheets("Loading File").Range("A" & lastrow + 1).pastespecial, paste:=xlvalues
            .Range("H1:K" & lrow).Copy 
Worksheets("Loading File").Range("H" & lastrow + 1).pastespecial, paste:=xlvalues