Suppose you have an Excel file with multiple tabs in the following format and you need to combine them to a single master sheet. This can either be a very long and boring copy/paste work or you can speed up the process by using the VBA code further down on this page.
| A | B | C | |
| 1 | Date | Account | Amount |
| 2 | 1/1/2016 | 484194 | $18,006.00 |
| 3 | 1/5/2016 | 497074 | $37,865.00 |
| 4 | 1/9/2016 | 321592 | $19,426.00 |
| 5 | 1/13/2016 | 784416 | $37,348.00 |
| 6 | 1/17/2016 | 904154 | $3,394.00 |
| 7 | 1/21/2016 | 936320 | $25,869.00 |
| 8 | 1/25/2016 | 596282 | $46,453.00 |
| 9 | 1/29/2016 | 257233 | $8,262.00 |
| 10 | 2/2/2016 | 640688 | $20,651.00 |
| 11 | 2/6/2016 | 169251 | $2,652.00 |
| 12 | 2/10/2016 | 204842 | $19,168.00 |
| 13 | 2/14/2016 | 403114 | $38,061.00 |
| 14 | 2/18/2016 | 932651 | $30,586.00 |
| Tab1 | |||
Don’t forget to like and share if you found the code useful!
Sub Combine_All_Tabs_to_Master_Sheet()
'https://www.chicagocomputerclasses.com/
'11/28/2016
'Chi Brander, Inc.
st = Application.InputBox("Please, enter a valid master worksheet name")
If Len(st) < 32 Then
mname = st & ""
Worksheets.Add.Name = mname
For Each ws In Worksheets
If ws.Name <> mname Then
ws.Select
If Range("A1").CurrentRegion.Rows.Count > 1 Then
Range("A1").CurrentRegion.Offset(1, 0).Select
Selection.Resize(Selection.Rows.Count - 1).Copy
Worksheets(mname).Select
Cells(Worksheets(mname).Range("a1").CurrentRegion.Rows.Count + 1, 1).Select
ActiveSheet.Paste
End If
End If
Next ws
End If
End Sub
Posted by Excel Instructor: