Results 1 to 7 of 7
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    More on Sheet Tab Names

    I have a workbook consisting of ten sheets. The leftmost sheet, named NewWk, contains a template that is used for up to 14 additional sheets, one of which will be added each week to the left of NewWk. The template is subject to change from time to time, so the new sheets can't be just all added at once.

    The new sheets are created by manually copying NewWk at the start of each week. The first one is named wk1, and is immediately to the left of NewWk. Subsequent sheets go to the left and are named wk2, wk3, etc. Once wk13 (or occasionally wk14) has been created, no more will be used on this workbook.

    I need a way to find the name of the most recently created sheet. (One of my other sheets wants to look at a specific cell on it.)

    I DO have a table that lists the dates on which each sheet SHOULD be created. I suppose I could use that somehow, but I'd rather check for the actual existence of the sheet, since SHOULD doesn't mean WAS.

    Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This should set the variable wks as the worksheet with the highest name.

    Steve

    Code:
    Dim wks As Worksheet
    Dim x As Integer
    Set wks = Nothing
    For x = 14 To 1 Step -1
      On Error Resume Next
      Set wks = Worksheets("wk" & x)
      On Error GoTo 0
      If Not wks Is Nothing Then Exit For
    Next

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Would you put that code under a button or some key combination for a macro? Could you also just find out how many sheets there are and calculate it from there? For my example below, the subtraction is because if there are 4 sheets, you know the last one you created is Wk3, etc. Something like:
    Dim x As Integer
    Dim wks As Worksheet
    Set wks as Nothing
    x = Worksheets.Count
    wks = Worksheets("wk" & x - 1)

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I forgot to say in my last post that I don't know if there is a Worksheets.Count property but it seems that there should be....

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Yes there is a Worksheets.count property

    Steve

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I used to know a lot of this stuff, but if you don't use it, you lose it...

    Assuming that Sdckapr's solution returns the proper value for the name of the highest numbered wkn worksheet, how do I get it to calculate or recalculate wks, and how do I put the value of wks into a cell?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To calculate a worksheet, after setting the wks variable, you can use the command:
    wks.calculate

    You can use a line like:
    Worksheets("NewWk").Range("A1").value = wks.name

    Change the worksheet and range as appropriate.

    Steve

Posting Permissions

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