Collecting All Data from multiple tabs in excel 2010

[philb99]

Hi - is there a very quick way to collect all data stored in the same spreadsheet but on multiple tabs and copy them into a new tab please.

All headings are the same in each

Thanks

 

[mumps]

Create a sheet and name it "Summary". Place all the headings in row 1. Place this macro in a standard module and run it from there.

Sub CopySheetData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Summary")
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            ws.UsedRange.Offset(1, 0).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[philb99]

Thanks but just getting error messages. Compile Error - Expected End Sub

I have created a new tab called "Summary" as advised - just in case all of my other tabs have different names though

What is a standard module please

 

[mumps]

Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. This will be your standard module. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. The error you received seems to indicate that the last line of the macro (End Sub) is missing. Please make sure that line is not missing.

 

[philb99]

Thanks again - I am now getting Run time error 9 - subscript out of range in the grey box

its highlighting the following part of the code with a yellow background

Set desWS = Sheets("Summary")

 

[mumps]

Do you mean a sheet with that name?

 

[philb99]

In the macro its your code that is being highlighted my sheet has been set up as "Summary"

Thanks

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[philb99]

Hi - I have now just taken out the "" at either end of Summary and all works perfectly - thanks for your help and support. This is just what I wanted - Brilliant

 

[mumps]

My pleasure.