Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Sheet names (Excel 2002)

    I thought I finally understood how to write my macros without having to use the actual sheet name (for example, Sheet1 is named "data"), and now the following code doesn't work. I must not really understand the naming concept yet. The actual worksheet names are long, and the names could be changed by the user down the road, so I want the macro to use the sheet number (ie Sheet1, Sheet2, Sheet3 etc.) Can anyone tell me what I've done wrong.
    Thanks so much!
    Lana
    For Each wsh In Worksheets
    Select Case wsh.Name
    Case Sheet1, Sheet3, Sheet4, Sheet5, Sheet6, Sheet13, Sheet28
    Case Else
    wsh.Select
    Range("A1").Select
    wsh.Copy
    With ActiveWorkbook
    'Save each worksheet with the date & worksheet name as the file name
    .SaveAs Filename:="G:SharedCatapult OverlaysRaw Materialtesting" & ActiveSheet.Name & ".xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

    End With
    End Select
    Next wsh

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Sheet names (Excel 2002)

    The Name of a sheet is a string, so values must be enclosed in quotes, and it represents the name displayed on the sheet tab.
    If you want to refer to the "other" name that is shown in the Visual Basic Editor, you must use the CodeName property:

    Select Case wsh.codeName
    Case "Sheet1", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet13", "Sheet28"

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Sheet names (Excel 2002)

    Ahh... love this stuff! This worked perfect... it makes sense now!
    Thanks Hans!
    Lana

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •