To get data from several cells on one sheet replicated across a the workbook into table

My problem

I want to extract multiple cell (specific cells) data from multiple sheets to make a table for easy analysis. I am trying VB as this would be the easiest and each sheet has 100 or so specific cells. Below is what I want to do, below that is the code I have found but cant figure out how to alter it to get it to retrieve additional lines. Can anyone help... thanks

What I am looking for is basically
Macros go to sheet2 and get data from C1, C5, D10, E15, C22 etc and get put data into sheet 1 across column.
Macros go to sheet3 and get data from C1, C5, D10, E15, C22 etc and get put data into sheet 1 across column, below previous data

.....
Does this make sense? And I will be adding additional sheets as the work goes on....


Sub MakeSummaryTable()
Dim ws As Worksheet

Application.ScreenUpdating = False
Sheets("Sheet1").Activate

For Each ws In Worksheets
If ws.Name <> "Sheet1" Then
ws.Range("C10").Copy
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
End If
Next ws

Application.ScreenUpdating = True

End Sub

Welcome to the forum Muthomi,

I don't think a macro is the best way to approach this.

I suggest you begin with Sheet1, and in row 1, enter the cell address across the page for all the cell references you want to extract from your subsequent sheets.

Thus, A1="C1", B1 = "C5", C1 = "D10", D1 = "E15", E1 = "C22" .... etc. (If you don't want to see this, you can hide it anywhere in your workbook.

Now, in A2 enter this formula: =INDIRECT("Sheet" & ROW() &"!"&A$1)

Copy it down and across, and you will have all the data from the other sheets populating your table.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".