Copy cells from all worksheets

[mits]

I have a workbook with 50 plus worksheets and will comtinue to grow. Each worksheet is copied from a template so they are exactly the same. I also have one worksheet that is consolidation page with a summary of all the info on the other worksheets. What I would like to be able to do is to copy the data from specific cells in each and all worksheets to columns in the consolidation page.

Please help, thanks in advance

 

[Von Pookie]

As an example:

Sub test()
Dim ws As Worksheet, PasteRng As Range

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
        With ws
            .Range("A1").Copy Destination:=PasteRng
        End With
    End If
Next ws

End Sub

This should copy the value from A1 of each page (but not the "Summary" sheet) and paste it to column A of a sheet named "Summary".

 

[Pekkavee]

Works great.

Thanks Von Pookie

I will keep this fo further use.

Pekka