Reputation: 1
Is there a way to copy cell ranges from multiple worksheets into another worksheet? For example:
Sheet1
Apple
Grapes
Peach
Cherry
Sheet2
Orange
Pear
Banana
Blueberry
Sheet3
Shekar
Mahesh
Rao
Gautham
I need the result as
Sheet4
Cherry
Blueberry
Gautham
Assume I have the data in Column A, B, C in the Sheet 1,2 & 3 and I need to display the combined results in Column A, B, C on Sheet4. Basically I need to display last row items from Sheet 1,2 & 3 in Sheet4 in three columns.
Upvotes: 0
Views: 205
Reputation: 17515
Try this code:
Sub Consolidate()
Dim rngTarget As Range
Set rngTarget = Sheets("YourTargetSheet").Range("A1:C1")
For i = 1 To 36
rngTarget.Value = Sheets("Sheet" & i).Range("A146:C146").Value
Set rngTarget = rngTarget.Offset(1)
Next
End Sub
Alternatively, if you use this formula in A1 and copy it 3 columns and 36 rows, you'll also get the result:
=INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Sheet"&ROW()))
Upvotes: 1