Listing sheet names on worksheet.
Posted by esteban malodieu on November 19, 2001 5:15 AM
I have a workbook with many sheets. I am trying to produce a macro that will print the sheet names of these sheets as a list in column A of a summary worksheet. I also want to print the value in cell J29 of each worksheet in column B of the same summary worksheet. Can anyone help?
Posted by RIck E on November 19, 2001 5:40 AM
Need more information...
What is the summary sheet name, what row do you want to start in and are there any other type of sheets (chart sheets) in the workbook besides worksheets?
Do you know how to get to the workbook module sheet?
That should do it.
Posted by Hodge on November 19, 2001 5:43 AM
Try this . . .
LastSheet = Worksheets.Count
CurrentSheet = 2 ' This assumse the summary is the first sheet
Worksheets(1).Range("A2").Activate
Do Until CurrentSheet > LastSheet
ActiveCell.Value = Worksheets(CurrentSheet).Name
SheetValue = "=" & Worksheets(CurrentSheet).Name & "!J29"
That should do the trick!
ActiveCell.Offset(0,1).Activate
ActiveCell.Formula = SheetValue
ActiveCell.Offset(1,-1).Activate
CurrentSheet = CurrentSheet + 1
Loop
