Copy cells from all worksheets

mits

New Member
Joined
May 2, 2003
Messages
16
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
As an example:
Code:
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

Well-known Member
Joined
May 25, 2004
Messages
1,218
Works great. :biggrin: :biggrin:

Thanks Von Pookie

I will keep this fo further use.

Pekka
:eek: :eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,070
Members
412,762
Latest member
sienweiw
Top