Macro to Combine Data from Multiple Tabs

[geliseev]

Hi All,

I get a daily data dump, which, unfortunately is spread out on numerous tabs due to limitations on the database pull. On any given day, I will have 15 tabs worth of data that I need to combine into one tab, so that I can then paste this data onto a template, which performs some calculations.

The data format on each of the tabs is the same.

To illustrate (with 3 tabs worth of data):
1) I have 100 rows of data on tab1 - row 5 through row 104.
2) I have 50 rows of data on tab2 - row 5 through row 54.
3) I have 70 rows of data on tab3 - row 5 through row 74.

Upon execution of the macro, there will only be one tab left in the Excel file and that tab will have all the data from the tabs above - i.e. it will have 100 + 50 + 70 = 220 rows of data from row 5 through row 224.

Any help in automating this task is appreciated.

Thanks!

 

[Trevor G]

Test this out.

Sub combSheet()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Sheet1").Activate
Range("A5").CurrentRegion.Copy
Sheets.Add
Range("A1").Activate
Range("A1").PasteSpecial xlPasteAll
Sheets("Sheet2").Activate
Range("A5").CurrentRegion.Copy
Sheets("Sheet4").Select
Range("A1").Activate
ActiveCell.Offset.End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial xlPasteAll
Sheets("Sheet3").Activate
Range("a5").CurrentRegion.Copy
Sheets("Sheet4").Select
Range("A1").Activate
ActiveCell.Offset.End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial xlPasteAll
Sheets(Array("Sheet1", "sheet2", "Sheet3")).Delete

Application.DisplayAlerts = True
Application.ScreenUpdating = True



End Sub

 

[geliseev]

Hmm, I get a "400" VBA error...

 

[Trevor G]

OK what are the sheet names you have in any of the workbooks? What version of Excel are you using?

 

[geliseev]

The sheets are named by the default Excel convention - Sheet1, Sheet2, etc.

I use Excel 2007, but the files that are automatically being pulled and saved from the database are saved in Excel 2003 (even though I open them with 2007). Would this have something to do with the error?

Thanks!

 

[Trevor G]

No it shouldn't matter lets try this out.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> debit1()<br><SPAN style="color:#007F00">'Combine all worksheets with in to the Summary sheet</SPAN><br><SPAN style="color:#007F00">'Created by Trevor G 30 June 2011</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> wsSummary <SPAN style="color:#00007F">As</SPAN> Worksheet<br>Sheets.Add<br>ActiveSheet.Name = ("Summary")<br><br><SPAN style="color:#00007F">Set</SPAN> wsSummary = Worksheets("Summary")<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Sheets<br><SPAN style="color:#00007F">If</SPAN> ws.Name <SPAN style="color:#00007F">Like</SPAN> "*Sheet*" <SPAN style="color:#00007F">Then</SPAN><br><SPAN style="color:#00007F">With</SPAN> ws<br>.UsedRange.Copy<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>wsSummary.Activate<br>Range("A1").Select<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br>ActiveCell.PasteSpecial xlPasteAll<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[geliseev]

Nope, still no luck. I tried to paste the raw data into a new .xlsm file as well to test it out there, but that also gave a 400 error message

No it shouldn't matter lets try this out.

 

[Trevor G]

OK question is are you using the VBA screen to Add a new module rather than trying to run it from a worksheet. ie have you tried using the right mouse button on a sheet name and select View Code if so this would be wrong...

In your workbook use Alt + F11 then Insert Menu and Module, then paste the code, and run it.

 

[geliseev]

Ah OK

Looking at the results of what this macro did - it seems like it is pasting the data from each tab on top of each other.

So, for example, if I have 50 rows on Sheet1 and 100 rows on Sheet2, after executing the macro, I am only seeing the 100 rows from Sheet2 on the Summary tab.

Instead, I should see the combined 150 rows of data.
Also, I think (not sure) it might be looping only through the first and last sheet and omitting the ones in between.

Does this make sense?

OK question is are you using the VBA screen to Add a new module rather than trying to run it from a worksheet. ie have you tried using the right mouse button on a sheet name and select View Code if so this would be wrong...

In your workbook use Alt + F11 then Insert Menu and Module, then paste the code, and run it.

 

[Trevor G]

I have just recorded this (and edited slightly) and it works.

Sub Macro1()
'Recorded and Edited
'19 July 2011
Sheets("Sheet1").Select
Range("A105").Select
Sheets("Sheet2").Select
Rows("5:54").Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Rows("5:74").Copy
Sheets("Sheet1").Select
Range("A155").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End Sub