locked
Excel 2003 - list worksheet names RRS feed

  • Question

  • I want to run a macro to list all of the sheets in a workbook and what the visible property is for each sheet.  I need to do this in an Excel 2003 workbook.  I use the code below successfully in Excel 2007 but get a Run-time error 13 on the "For Each ws In Worksheets" line when I try to run it in Excel 2003.  What needs to be changed to run this in Excel 2003?

    Sub ListSheets()

    Dim ws As Worksheets
    Dim x As Integer

    x = 1

    Sheets("SheetNames").Range("A:C").Clear

    For Each ws In Worksheets
        Sheets("SheetNames").Cells(x, 1) = ws.Name
        Sheets("SheetNames").Cells(x, 2) = ws.Visible
        Sheets("SheetNames").Cells(x, 3) = ws.CodeName
       
        x = x + 1
       
    Next ws

    End Sub

    Wednesday, March 9, 2011 11:15 PM

Answers

  • I'm not sure if the first response made it through...

     

    Drop the final "s" in this line:

    dim ws as worksheet 'not worksheetS

     

     

    • Marked as answer by LSZ1 Thursday, March 10, 2011 12:38 AM
    Thursday, March 10, 2011 12:28 AM

All replies

  • I'm not sure if the first response made it through...

     

    Drop the final "s" in this line:

    dim ws as worksheet 'not worksheetS

     

     

    • Marked as answer by LSZ1 Thursday, March 10, 2011 12:38 AM
    Thursday, March 10, 2011 12:28 AM
  • That was it.  Thank you.
    Thursday, March 10, 2011 12:38 AM