Summarizeing sheets

J

Johnny

What I am trying to do is to summarize all sheets in the workbook onto
a "summary" tab
This summary should have the format of:
On Summary Sheet
<B1>Sheet a's Name
<B2>Sheet a's First Row
<B3>Sheet b's Name
<B4>Sheet b's First Row
I am not great at vb style programming. Here is the pseudo style
code. Any help makeing this code to become working is appreciated.
This is for Excel 97.
Sub CreateSummaryOfWorkbooks()
Dim wsName As String
X = 1
For Each Sheet In Sheets
Sheet.Select
wsName = Worksheet.Name ' problem setting name into string
Cells(A, 1).Select
Selection.EntireRow.Copy
Worksheets("Summary").Select
Cells(B, X) = wsName
Cells(B, X + 1).Paste
X = X + 2
Next
End Sub

Thanks
 
F

Frank Kabel

Hi
not tested but try
Sub CreateSummaryOfWorkbooks()
Dim wsName As String
Dim wks as worksheet
Dim target_sheet as worksheet
Dim X

set target_sheet=Worksheets("Summary")
X = 1
For Each wks In worksheets
wsName = wks.name
if lcase(wksname)<>"summary" then
target_wks.Cells(X,"B").value = wsName
target_wks.cells(X+1,"B").value=wks.cells(1,"A").value
X = X + 2
end if
Next
End Sub
 
Top