Get month name from date to use as Worksheet name

[Derick63]

Hello again. I am in over my head again and could use some help after a lot of searching.
On the Active Sheet (Employee Pay Slip), cell (D2) has a date in it . I'm trying to extract the Month from the date and assign it to a variable as the name of a worksheet where I need to extract a list of employees from. In essence, I'm trying to get those employees names into cells of each pay slip E10, O10, E27, O27 through E214 & O214 (17 rows apart). Two Slips are shown side by side and I have 26 slips total. After the name extraction is placed in Column V1, the Module counts the amount of names and puts that count into H3 (EntireRow.Hidden = True/False). This part works fine. The problem is getting the month name from a date to use as a variable.
Since it wasn't working for me I tried extracting the month with a formula then referring to that cell H2
I'm learning so any help would be great. Also, if you see anything that can be modified to streamline the process I'm totally open to all mods.
Thanks in advance guys.

Sub ClrCpyPyRl()
    
    Dim MthCell As String
    Dim wsMth As Worksheet
    Dim wsEmpPySlp As Worksheet
    Dim mthLR As Long
    Dim mthHdrRow As Long
    Dim Count As Long

    'On Error Resume Next '(Off to find error)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
'------------------------------------------------------------------------------------
'Get Name of month from date cell (D2) to use as Worksheet name
    MthCell = Range("H2").Value 'Cell H2 has a formula =TEXT($D$2,"mmmm") extracting the month from D2
    'MthCell = MonthName(Range("D2")) 'Invalid Procedure Call or Argument
    Set wsMth = Worksheets(MthCell)
'------------------------------------------------------------------------------------
    Set wsEmpPySlp = Sheets("Employee Pay Slip")
    mthHdrRow = 8 'Header Row No. on Month sheet
   
    With wsMth
        .Unprotect Password:=""
        mthLR = .Range("C" & Rows.Count).End(xlUp).Row
        ' Check for no data on Month sheet
        If mthLR > mthHdrRow Then
'------------------------------------------------------------------------------------
'Sheet (wsMth) where data is copied from
            Sheets(wsMth).Range("C8:C" & mthLR).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheets(wsEmpPySlp).Range("C2:D2"), CopyToRange:=Range("'Employee Pay Slip'!Extract"), _
            Unique:=False
'------------------------------------------------------------------------------------
            Count = Range("V2").End(xlDown).End(xlDown).End(xlUp).Rows.Count
            Range("H3").Value = Count
        End If
    End With
    
    Sheets(MthCell).Protect Password:=""
    Sheets("Payroll").Protect Password:=""
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[Derick63]

TheMonthName = Format(YourDateValue, "mmmm")

Thanks Mr. Rothstein. It got pass that line of coding but got hung up on later in the code. Runtime Error 13: Type mismatch. I'm probably incorrect but using the variable assigned to the String then using it to make it a Worksheet Object doesn't work. I'm learning.

Runtime Error 13: Type mismatch.

    Dim MthCell As String
    Dim wsMth As Worksheet

    MthCell = Format(Range("D2"), "mmmm")
    Set wsMth = Worksheets(MthCell)

     Sheets(wsMth).Range("C8:C" & mthLR).AdvancedFilter Action:=xlFilterCopy, _
     CriteriaRange:=Sheets(wsEmpPySlp).Range("C2:D2"), CopyToRange:=Range("'Employee Pay Slip'!Extract"), _
     Unique:=False

 

[johnnyL]

You cant Set wsMth = Worksheets(MthCell) If that sheetname doesn't exist yet.

 

[Peter_SSs]

wsMth already is a worksheet so
Sheets(wsMth).Range("C8:C ...
wsMth.Range("C8:C ...

 

[Derick63]

.....while MonthName is only expecting a numerical argument from 1 to 12.

So, apart from some other ways suggested above, you could have used the MonthName function as follows

MthCell = MonthName(Month(Range("D2").Value))

Thanks for your suggestion and it worked as well as Mr. Rothstein suggestion. I can't tell the difference in efficiency or in one being better. Lack of experience.
For clarity, doesn't MonthName returns a name and Month returns a number?

 

[Peter_SSs]

I can't tell the difference in efficiency or in one being better.

I don't think that one is better then the other. I was really posting to explain why your original attempt was throwing an error.

For clarity, doesn't MonthName returns a name and Month returns a number?

Correct

 

[Derick63]

wsMth already is a worksheet so
Sheets(wsMth).Range("C8:C ...
wsMth.Range("C8:C ...

Absolutely right! Thank you

I don't think that one is better then the other. I was really posting to explain why your original attempt was throwing an error.


Correct

Your explanation is understandable and your solution worked and it was elsewhere also. I corrected it and it worked. I'll mark it as the solution. Thanks again and it's appreciated

 

[Peter_SSs]

You're welcome. Glad we could help.

 

[Derick63]

You cant Set wsMth = Worksheets(MthCell) If that sheetname doesn't exist yet.

Fixed that issue which caused that line to work. Had another issue further down the code where I was calling the variable to be a worksheet and it was already a worksheet. Thanks.