Forum Discussion
Solved
How combined data from multiple sheets to one sheet.
I have a document with 7 sheets with data. Each have the same 4 column headers and each vary in the number of rows from as many as 2500 on one and only 5 on another. What I want to do it have al...
Try this macro:
Sub CombineSheets() Dim wshS As Worksheet Dim wshT As Worksheet Dim i As Long Dim t As Long Dim rng As Range Application.ScreenUpdating = False Set wshT = Worksheets.Add(Before:=Worksheets(1)) t = 1 For i = 2 To Worksheets.Count Set wshS = Worksheets(i) Set rng = wshS.UsedRange If i > 2 Then Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1) End If rng.Copy Destination:=wshT.Range("A" & t) t = t + rng.Rows.Count Next i Application.ScreenUpdating = True End Sub
Try this macro:
Sub CombineSheets()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim i As Long
Dim t As Long
Dim rng As Range
Application.ScreenUpdating = False
Set wshT = Worksheets.Add(Before:=Worksheets(1))
t = 1
For i = 2 To Worksheets.Count
Set wshS = Worksheets(i)
Set rng = wshS.UsedRange
If i > 2 Then
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
End If
rng.Copy Destination:=wshT.Range("A" & t)
t = t + rng.Rows.Count
Next i
Application.ScreenUpdating = True
End SubThis worked great. I do get an error message but I just select "Ok" and it appears to have done what I wanted it to do.
This is awesome, thanks so much!
What did the error message say?
