summary sheet?

  • Thread starter Thread starter Ket
  • Start date Start date
K

Ket

I am using Excel 2K and Windows 2K.

I have a set of worksheets in a workbook that are identically
formatted.
Is it possible to create a summary sheet that will automatically place
the worksheet name in one column and information next to it from say
cells A1,C1,F30 from all worksheets.

I'm not a great VB whizz.

Any help greatly appreciated.

TIA

Ket
 
This will add a new sheet and create the list. Press
ALT+F11, go to Insert > Module, and paste in the code.
Then run the macro:

Sub Summary()
Dim ws As Worksheet
Dim i As Integer
i = 2
Sheets(1).Activate
Sheets.Add
With Sheets(1)
.Range("A1").Value = "Sheet Name"
.Range("B1").Value = "Cell A1"
.Range("C1").Value = "Cell C1"
.Range("D1").Value = "Cell F30"
For Each ws In ThisWorkbook.Worksheets
If ws.Index <> 1 Then
.Rows(i).Cells(1).Value = ws.Name
.Rows(i).Cells(2).Value = ws.Range("A1")
.Rows(i).Cells(3).Value = ws.Range("C1")
.Rows(i).Cells(4).Value = ws.Range("F30")
i = i + 1
End If
Next
End With
End Sub
 
Hi Ket
in addition so Jason's VBA solution you can have a similar
functionality with only using formulas. You only have to enter your
worksheet names manually. Lets assume the worksheetnames are stored in
column A. enter the following in b1:
=INDIRECT("'" & A1 & "'!A1")
(note the multiple apostrophes). copy this down for all your
worksheetnames in column A. This will get the values from cell A1 for
each worksheet

Frank
 
Back
Top