Macro to copy all worksheet data to a summary worksheet on the same file

[joycesolomon]

hi

I need some help.
I have a excel file that has many sheets. eg sheet1 - sheet100
I also have three others called sheetA, sheetB and sheetC

I need a macro to extract data from sheet1-sheet100 to be placed in a summary sheet...can someone please help.
I been searching and trying some codes but nothing seems to work for me.
Thanks

 

[John_w]

Maybe this example code will help to get you started. It copies cells A1:C6 (i.e. 6 rows) on Sheet1 to Sheet100 to the Summary sheet starting at A1 on the Summary sheet.

Sub Copy_Cells_To_Summary_Sheet()

    Dim i As Integer
    Dim rowOffset As Long
    
    rowOffset = 0
    For i = 1 To 100
        Sheets("Sheet" & i).Range("A1:C6").Copy Sheets("Summary").Range("A1").Offset(rowOffset, 0)
        rowOffset = rowOffset + 6
    Next
    
End Sub

If you need further help, please state exactly which cells on Sheet1 to Sheet100 should be copied and to where on the Summary sheet.

 

[joycesolomon]

hi,

i copied your code to a button click and tried running it, it gives out error:

Run time error 9
Subscript out of range

Please help

 

[joycesolomon]

i managed to get the code running.
But how do I only do for sheet named 1 through 100
I got other sheets like SheetA, SheetB that it is also doing the copy over

 

[John_w]

But how do I only do for sheet named 1 through 100
I got other sheets like SheetA, SheetB that it is also doing the copy over

The code should only copy cells from sheets named Sheet1, Sheet2, Sheet3, ... Sheet100 (sheet names are case-insensitive so it should also work if they are named sheet1, sheet2, etc.) and ignore all other sheets, for example those named SheetA and SheetB. What are the exact names of your sheets?

 

[joycesolomon]

Hey,

thanks for the clarification , but i managed the whole process and have a working code now