Creating a list of info in A1 cell in multiple worksheets

  • Thread starter Thread starter bosch711
  • Start date Start date
B

bosch711

How can I create a list of the info/contents in the A1 cell in multiple
worksheets? I am trying to create a summary worksheet of the data from 90
worksheets and would like to be able to list the headers in the A1 cell
vertically.
 
'Right click on the summary sheet tab, and paste in this macro.
'Edit where appropriate:

Sub CreateSummary()
'Starting row for summary:
i = 1
For Each Sheet In ThisWorkbook.Sheets
If Sheet.Name <> Me.Name Then

'Control What column to place data in
Me.Cells(i, "A").Value = Sheet.Range("A1").Value
i = i + 1
End If
Next
End Sub
 
It's easy if the sheets are named sheet1, sheet2, etc:., otherwise you'd
probably need a macro. If the former case:
=INDIRECT("Sheet"&ROW(A2)&"!A1") and fill down - starting with A2 for the
2nd sheet else you'd get a circular reference on Sheet1.
HTH
 
Luke,

I pasted the macro into the summary tab but I don't know what items to edit
to make it work. Any help would be MUCH appreciated.

Thanks
Bosch
 
Bob,

Thanks. I am changing my worksheet names because I have yet to get a macro
to work but your help below worked beautifully... Thank you!
 
Back
Top