I am downloading a report into excel on a weekly basis. It downloads into about 50 sheets in one workbook and I cannot change that so I am looking for a macro that will combine them into one long sheet. I would like it to create a new sheet and copy/paste each row (all columns or A-Q if it matters) from sheet 1, then on the next line start with row 1 from sheet 2, and so on. Each sheet has a different number of rows, but the last row on each sheet will always be the last row that has data in column G. The number of sheets could also change from week to week. I tried revising a code found in an old thread but I don’t know how to tell it to copy from row 1 down to the last row w/ data in “G”. Thanks for any help you can provide!
Option Explicit
Sub CombineWorksheets()
'Declare the variables
Dim wksCombined As Worksheet
Dim wks As Worksheet
Dim CalcMode As Long
Dim LastRow As Long
'Add a new worksheet before the first worksheet in the active workbook
Set wksCombined = Worksheets.Add
'Name the new worksheet
wksCombined.Name = "Combined"
'Loop through each worksheet within the active workbook
For Each wks In ActiveWorkbook.Worksheets
'Skip the new worksheet
If wks.Name <> "Combined" Then
With wks
'Find the last used row in Column B
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
'Copy the data from the current worksheet to the first available row of the new worksheet starting at Column B
.Range("B6", .Cells(LastRow, "R")).Copy Destination:=wksCombined.Cells(wksCombined.Cells.Rows.Count, "B").End(xlUp).Offset(1)
End With
End If
Next wks
'Display a message indicating that the macro has finished
MsgBox "Completed...", vbInformation
End Sub
Option Explicit
Sub CombineWorksheets()
'Declare the variables
Dim wksCombined As Worksheet
Dim wks As Worksheet
Dim CalcMode As Long
Dim LastRow As Long
'Add a new worksheet before the first worksheet in the active workbook
Set wksCombined = Worksheets.Add
'Name the new worksheet
wksCombined.Name = "Combined"
'Loop through each worksheet within the active workbook
For Each wks In ActiveWorkbook.Worksheets
'Skip the new worksheet
If wks.Name <> "Combined" Then
With wks
'Find the last used row in Column B
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
'Copy the data from the current worksheet to the first available row of the new worksheet starting at Column B
.Range("B6", .Cells(LastRow, "R")).Copy Destination:=wksCombined.Cells(wksCombined.Cells.Rows.Count, "B").End(xlUp).Offset(1)
End With
End If
Next wks
'Display a message indicating that the macro has finished
MsgBox "Completed...", vbInformation
End Sub
