Collecting All Data from multiple tabs in excel 2010

philb99

Active Member
Joined
Feb 3, 2014
Messages
312
Office Version
2010
Platform
Windows
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,906
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.
Code:
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
 
Last edited:

philb99

Active Member
Joined
Feb 3, 2014
Messages
312
Office Version
2010
Platform
Windows
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
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,906
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

Active Member
Joined
Feb 3, 2014
Messages
312
Office Version
2010
Platform
Windows
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")
 
Last edited:

philb99

Active Member
Joined
Feb 3, 2014
Messages
312
Office Version
2010
Platform
Windows
In the macro its your code that is being highlighted my sheet has been set up as "Summary"

Thanks
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,906
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

Active Member
Joined
Feb 3, 2014
Messages
312
Office Version
2010
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,724
Messages
5,446,148
Members
405,384
Latest member
geowbadyt

This Week's Hot Topics

Top