Find worksheet with variable name

[camandab]

Hello. I have a series of templates that I'm working with. Each template will contain at least 1 worksheet for the current month of data. I need to copy the previous month's worksheet and the name will be in format "MMM YY". I can't always retrieve the last sheet as there may be a supporting worksheet from the previous month that will occupy the last sheet in the workbook so this is why I need to find the previous month using that format. Also there is a 1 month lag so if I'm working this process in the calendar month of April, I will need to find the sheet named "Feb 10", etc. Is there anyway I can code this using vba? Any help will be much appreciated!!

 

[Joe4]

Also there is a 1 month lag so if I'm working this process in the calendar month of April, I will need to find the sheet named "Feb 10"

If there is a one month lag and it is currently the month of April, wouldn't you want to look at March and not February (February would be a two month lag)?

In any event, if you wanted to activate the sheet from last month, you could do so with VBA code like this:

Sub mySheetSelect()
 
    Dim mySheet As String
    
    On Error GoTo err_fix
    mySheet = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "MMM YY")
    Sheets(mySheet).Activate
    On Error GoTo 0
    
    Exit Sub
    
err_fix:
    If Err.Number = 9 Then
        MsgBox "There is no sheet named " & mySheet
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
        
End Sub

 

[camandab]

Excellent! Works great!! Thanks so much for your help!