Transform data from multiple sheets

Question

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.

I do not understand what column has to go where. Can you please specify?! And: does it have to be VBA? Looks like a plain excel formula to me... Last but not least: What have you tried? — Peter Albert
Column A, B, C in Row 146 (i want the data in 146th row in every sheet i.e. 36 sheets into a new worksheet or workbook) — Gowtham Kumar
is it always row 146 or does this depend on the worksheet? and: are the worksheets named "sheet1", "sheet2", etc.? — Peter Albert
it's always row 146 in every sheet & worksheet named as "sheet1" "sheet2" ..... — Gowtham Kumar

Peter Albert Peter Albert · Accepted Answer · 2013-02-26T12:18:12

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()))

Transform data from multiple sheets

1 Answers