Copy first row of data from all worksheets onto one

[leeksleeks]

Hi,

I am needing a VBAcode that will copy data from Cells A1:G1 on all worksheets and paste this onto one 'Results' worksheet. I have a file without thousands of worksheets on them but the data is only on the first row.

Any help you be greatly appreciated.

Cheers,

Alex

 

[BrianMH]

Add this code to a module in the workbook you want to combine data from. This assumes a results sheet already exists.

Sub combinesheets()
    Dim wsCopy As Worksheet, wsPaste As Worksheet
    Set wsPaste = Sheets("Results")
    Dim rPaste As Range
    Set rPaste = wsPaste.Range("A" & wsPaste.Rows.Count).End(xlUp)
    If rPaste <> "" Then rPaste = rPaste.Offset(1)


    For Each wsCopy In ThisWorkbook.Worksheets
        If wsCopy.Name <> "Results" Then
            wsCopy.Range("A1:G1").Copy rPaste
            Set rPaste = rPaste.Offset(1)
        End If
    Next wsCopy
End Sub

 

[leeksleeks]

Thank you Brian. That has worked a treat!!!

 

[BrianMH]

Glad to help!