Need help creating a list of information from a workbook

J

Joeb

I have a workbook with about 100 worksheets in it and I need to create a new worksheet that will display a list of all the information held in each B1 field and Each B28 Field...how can i do that?
 
J

Jason Morin

Modified some old code I found. Press ALT+F11, Insert >
Module, and paste this in. Toggle back to XL and run the
macro:

Sub ListCellContents()
Dim ws As Worksheet
Dim i As Integer
i = 1
Sheets(1).Activate
With Sheets.Add
For Each ws In ThisWorkbook.Worksheets
.Rows(i).Cells(1).Value = ws.Name
.Rows(i).Cells(2).Value = ws.Range("B1")
.Rows(i).Cells(3).Value = ws.Range("B28")
i = i + 1
Next
End With
Sheets(1).Rows("1:1").Delete
End Sub

--
HTH
Jason
Atlanta, GA
-----Original Message-----
I have a workbook with about 100 worksheets in it and I
Click to expand...
need to create a new worksheet that will display a list
of all the information held in each B1 field and Each B28
Field...how can i do that?
 
B

BrianB

Say you have a list of sheet names in column A.

In column B put the formula : =INDIRECT($A1 &"!B1")
In column C put the formula : =INDIRECT($A1 &"!B28")

and copy down
 
Top