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

    Macro - Sheet Name (Excel 2002)

    Hi,

    I'd like to change the code in one of my macros to refer to Sheet3, as opposed to the name I have renamed it to. For example, I've renamed "Sheet3" to be "Data". In my macro I have it select the Data worksheet per the following code:
    With Worksheets("Data")

    When I change the code to the following code below, it doesn't work:
    With Worksheets("Sheet3")

    I've run into problems with my macros because people have the need to change the worksheet name, and then the macro doesn't work, so I thought if I had the macro code use the original sheet name I could avoid this problem.

    Thanks!!
    Lana

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

    Re: Macro - Sheet Name (Excel 2002)

    You can give the sheet a special name to be used in code:
    - Activate the Visual Basic Editor.
    - Click on the sheet in the Project Explorer on the left.
    - Change the (Name) property to - for example - MySheet.
    - You can now refer to MySheet.Range("A1") instead of Worksheets("...").Range("A1").
    - Note that you use the code name directly, without using Sheets( ) or Worksheets( ).

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Sheet Name (Excel 2002)

    Try:

    <code>
    With Sheet3
    MsgBox .Name
    End With
    </code>
    Legare Coleman

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

    Re: Macro - Sheet Name (Excel 2002)

    Cool... just writing Sheet3.Select worked. Hans, I tried your method, and it changed the actual name of the worksheet when I viewed it in Excel. On the surface, this seems to be the same as just renaming the worksheet?? I must be missing something? I'd like to understand so that I could make use of this knowledge for future projects.
    Thanks so both Hans & Legare for responding!!
    Lana

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

    Re: Macro - Sheet Name (Excel 2002)

    There is a distinction between the (Name) and Name properties of a worksheet. The (Name) property is the code name, and the Name property is the caption of the worksheet tab.
    Attached Images Attached Images
    • File Type: png x.png (5.4 KB, 1 views)

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

    Re: Macro - Sheet Name (Excel 2002)

    Ahh.. this makes sense now! I was changing the "Name" as opposed to the "(Name)".
    Thanks Hans!
    Lana

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

    Re: Macro - Sheet Name (Excel 2002)

    I changed the (name) to "Lana" in the properties area as you suggested, but when I change the code below (where it says Case "Data") to say Case "Lana" it doesn't work. I've tried Case (Lana) and Case Lana, and to no avail all three ways... likewise I tried Case "Sheet3", Case Sheet3, and Case (Sheet3)... it must not work when doing a else function??
    Thanks!
    Lana

    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "Data", "Save Reports"
    Case Else
    wsh.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    End Select
    Next wsh

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Sheet Name (Excel 2002)

    You change the sheet's code name to Lana. Then you used the Name property in the Select Case statement, and the Name property is the worksheet name that is displayed on the worksheet tab, not the code name. If you changed the Code Name, then you would have to use the wsh.Codename property in the Select Case statement.
    Legare Coleman

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

    Re: Macro - Sheet Name (Excel 2002)

    I changed the "Case" area below to reflect your suggestion... I must be doing it wrong, as it still doesn't work. The (name) of the worksheets in the properties is "Sheet3" and the other one is "Lana". The actual names of the worksheet tabs are "Data" and "Save Reports". Any ideas how to fix?
    Thanks!
    Lana

    For Each wsh In Worksheets
    Select Case wsh.Name
    Case wsh.Sheet3, wsh.Lana
    Case Else
    wsh.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    End Select
    Next wsh

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Sheet Name (Excel 2002)

    Does this do what you want?

    <code>
    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "Lana"
    Case Else
    wsh.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    End Select
    Next wsh
    </code>
    Legare Coleman

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

    Re: Macro - Sheet Name (Excel 2002)

    Hi Legare... thanks for the suggestion! I'm still having problems with it though, as it is still hanging up because of the sheet (name). From the original suggestions earlier in this thread, I've been able to get it to work, but it just won't work when I use it after the command "Case"???
    Lana

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

    Re: Macro - Sheet Name (Excel 2002)

    wsh.Sheet3 and wsh.Lana make no sense - wsh is a worksheet object, and Sheet3 or Lana are not properties of a worksheet object. Moreover, since wsh.Name is a text string, the Case statement expects string values. Try this:

    Select Case wsh.CodeName
    Case "Sheet3", "Lana"
    ' Do nothing
    Case Else
    ...
    End Select

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Sheet Name (Excel 2002)

    Does this do what you want?

    <code>
    For Each wsh In Worksheets
    Select Case wsh.CodeName
    Case "Lana"
    Case Else
    wsh.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    End Select
    Next wsh
    </code>

    I wasn't sure which name you had changed to Lana. The above uses the CodeName, and the previous used the sheet name.

    If that still does not do what you want, could you upload a workbook that shows the problem? You can delete all of the data.
    Legare Coleman

Posting Permissions

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