select worksheet based on month

  • Hello,


    New to VBA, and making slow progress.
    I am trying to make my life easier by coding a long winded process to transfer stats from the current week to the sheet for the current month. So, all stats for week commencing 25 Feb, should be transferred to the worksheet February, then deleted from the original sheet and the date updated. I have managed to do all of this, but I am struggling to get the code to select the sheet with the month automatically.


    I have managed to get the month extracted from the cell holding the value for the week commencing date, but do not know how to use that to determine the sheet I want to select


    I have tried the following (Sheet Z1 Cell B:17 holds the relevant month as text):


    Dim strSheet As String

    Sheets(Sheets(Z1).Range("B17")).Activate


    When I run the macro, I keep getting the error: Runtime error 9: Subscript out of range.


    Does anybody have an idea how I can do this?
    I tried to attach the (very basic) spreadsheet, but somehow couldn't..


    Thanks,
    Roland

  • Re: select worksheet based on month


    Hi Roland,


    I'm new to this also, but I think the answer to your question is on a previous thread:


    HTML
    http://www.ozgrid.com/forum/showthread.php?t=59691


    Please note, this code does not go by Sheet name, it goes by the sheet reference so if you change the name of the sheet, the code will not be affected.


    Right Click on the tab you have names Z1, select 'View Code' and this should say something like "Sheet1 (Z1)", change the sheet number in the code to reflect your worksheet.


    Code
    Sub SelectWorkheet()
        Dim strWsName As String
         
        strWsName = Sheet1.Range("B17")
        Sheets(strWsName).Select
    End Sub


    I've not tested it, as I found it on a previous thread.


    Hope this helps!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!