Summary of many tabs

S

Scorex Ed

Joined
May 14, 2008
Messages
1
Reaction score
0
Hi,



I’m trying to make a summary table containing Tab name and a value in A17 for a variable number of tabs. So far my code reads:



Code: 
[/size][/font]

[size=3][font=Times New Roman]Sub ListSheetNames()[/font][/size]

[size=3][font=Times New Roman]	Dim oSheet As Object[/font][/size]

[size=3][font=Times New Roman]	 [/font][/size]

[size=3][font=Times New Roman]	For Each oSheet In Sheets[/font][/size]

[size=3][font=Times New Roman]		If oSheet.Name <> "Summary" Then[/font][/size]

[size=3][font=Times New Roman]			With Sheets("Summary")[/font][/size]

[size=3][font=Times New Roman]				.Cells(.Rows.Count, "A").End(xlUp)(2, 1) = oSheet.Name[/font][/size]

[size=3][font=Times New Roman]				.Cells(.Rows.Count, "B").End(xlUp)(2, 1) = A17[/font][/size]

[size=3][font=Times New Roman]			End With[/font][/size]

[size=3][font=Times New Roman]		End If[/font][/size]

[size=3][font=Times New Roman]	Next oSheet[/font][/size]

[font=Times New Roman][size=3]End Sub[/size][/font]

[font=Times New Roman][size=3]



This creates the list of tab names, but doesn’t bring back the values in A17. Any idea what I’m doing wrong? I’ve stolen most of this code from various Excel sites so I don’t really know what the specific bits actually do…



Many thanks,

Ed
 
Back
Top