CODEWITHSUNDEEP
excelvba
Gowtham Kumar
Gowtham Kumar

Reputation: 1

Transform data from multiple sheets

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

Answers (1)

Peter Albert
Peter Albert

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

Related Questions