Referring to a variable-named worksheet-CodePudding

I am having trouble using a variable worksheet name in other areas of my code. I would like to hide a worksheet based on the name, which uses the date in a particular cell. Here is the applicable code.

Sub StartMedCount()

Dim actionname As String
actionname = "Action List " & Sheets("MedicationCounts").Range("C2").Value

ThisWorkbook.Worksheets(actionname).Select
    ActiveWindow.SelectedSheets.Visible = False

end sub

I have a sheet already named "Action List 11-24-2021" and cell C2 contains 11-24-2021. I get a Runtime error 9 - subscript out of range. Can you help me fix this?

CodePudding user response：

I tried this below and it works for me.

Sub StartMedCount()
    Dim ws As Worksheet
    Dim ActioNname As String
    
    Set ws = ThisWorkbook.Worksheets("MedicationCounts")
    ActioNname = "Action List " & ws.Range("C2").Value
    
    ThisWorkbook.Worksheets(ActioNname).Visible = False

End Sub

CodePudding user response：

Thank you for your input! I was able to troubleshoot it with a coworker and we found the following fix:

The date in C2 was formatted in mm-dd-yyyy format but the true value was mm/dd/yyyy. We changed the format to text and inputted it using hyphens. Elsewhere in the code when I add that date, I use Format to change it to hyphens before it's entered into the cell.