Copying data from multiple worksheets

[Alburne]

Hi, I have circa 500 sheets (all with identical layout) in the same workbook. All the data is displayed in Column "A", with the number of entries (5-6 digit numbers) varying between 1 and 50. I am trying to copy the entries from column A in each sheet and display it as a full list in a new sheet (circa 5000 entries in 1 column).

thanks in advance

 

[njimack]

This assumes your data starts from A1 on each sheet, and you want it posted to Sheet1 of Book2.

Sub Copy_Data()

    Dim Sheet As Worksheet
    Dim sh As String
    Dim End_Row As Long

    For Each Sheet In ThisWorkbook.Sheets
        sh = Sheet.Name
        End_Row = Sheets(sh).Range("A" & Rows.Count).End(xlUp).Row
        Sheets(sh).Range("A1:A" & End_Row).Copy Workbooks("Book2").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next Sheet

End Sub

 

[Alburne]

Hi, thanks, sorry for being stupid but how/where do I enter the code? Using excel 2007 (in compatability mode)

 

[njimack]

1. Press Alt+F11 to enter the Visual Basic Explorer
2. On the left hand-side of the screen, you should see "VBAProject (Book1)", assuming your file is called Book 1
3. Right-click this and select 'Insert Module'
4. In the big window on the right, paste the code I gave you
5. Exit the Visual Basic Explorer by clicking the 'X' at the top-right of the screen

I'm doing the next bit by memory since I'm not on a 2007 machine -

you need to make sure the developer tab is activated. Click the big Office button (top-left of your screen) and then click Excel options. Click Popular and then 'Show Developer tab in Ribbon'

From the Developer tab you will be able to run the macro called 'Copy_Data'

 

[Alburne]

Thanks Neil, you're a genius.