tatendamark
09-15-2017, 04:29 AM
How would you simultaneously:
1. Copy different range of values from multiple worksheets e.g. Range("E3 : G3").Copy and Range("I3 : DA3").Copy, and paste them into one worksheet "Original Data", one row after the other in the same columns e.g. Column("E:E") and Column("I:I").
2. Sum the range of values from multiple worksheets e.g. Range("H3:H800") and Range("I3:I800"), and paste the result into one worksheet "Original Data", one row after the other.
My attempt, without the summing, but the rows are not copying over correctly, I suspect because of the offset function and Range("I3 : DA3").Copy:
Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Original Data" Then
Sht.Select
Range("E3 : G3").Copy
Range("I3 : DA3").Copy
Sheets("Original Data").Select
Range("E65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True
Else
End If
Next Sht
End Sub
Your assistance is greatly appreciated.
1. Copy different range of values from multiple worksheets e.g. Range("E3 : G3").Copy and Range("I3 : DA3").Copy, and paste them into one worksheet "Original Data", one row after the other in the same columns e.g. Column("E:E") and Column("I:I").
2. Sum the range of values from multiple worksheets e.g. Range("H3:H800") and Range("I3:I800"), and paste the result into one worksheet "Original Data", one row after the other.
My attempt, without the summing, but the rows are not copying over correctly, I suspect because of the offset function and Range("I3 : DA3").Copy:
Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Original Data" Then
Sht.Select
Range("E3 : G3").Copy
Range("I3 : DA3").Copy
Sheets("Original Data").Select
Range("E65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True
Else
End If
Next Sht
End Sub
Your assistance is greatly appreciated.