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

 

[My Aswer Is This]

Assuming you have a Date in Range("D2") of the active sheet use this:

Private Sub CommandButton1_Click()
'Modified  7/20/2022  8:40:18 PM  EDT
Dim ans As Long
ans = Month(Range("D2").Value)
MsgBox ans
End Sub

 

[Rick Rothstein]

TheMonthName = Format(YourDateValue, "mmmm")

 

[Derick63]

TheMonthName = Format(YourDateValue, "mmmm")

Hi and Thanks for the reply, I tried that and commented it out with Error message
"

MthCell = MonthName(Range("D2")) 'Invalid Procedure Call or Argument

 

[johnnyL]

If you follow what @Rick Rothstein said, your code would look like:

    MthCell = Format(Range("D2"), "mmmm")

 

[Derick63]

Assuming you have a Date in Range("D2") of the active sheet use this:

Private Sub CommandButton1_Click()
'Modified  7/20/2022  8:40:18 PM  EDT
Dim ans As Long
ans = Month(Range("D2").Value)
MsgBox ans
End Sub

Hi. Thanks. I was looking to grab the name and tried using MthCell = MonthName(Range("D2")) and the error message was "Invalid Procedure Call or Argument".

The way I learned, so far, is to declare the MonthName from Date cell D2 as a String then as a variable then use that variable as the object Worksheet as I attempted to do here but not working.

    Dim MthCell As String
    Dim wsMth As Worksheet

    MthCell = Range("H2").Value 'Cell H2 has a formula =TEXT($D$2,"mmmm") extracting the month from D2
    'MthCell = MonthName(Range("D2")) 'Error Message - Invalid Procedure Call or Argument
    Set wsMth = Worksheets(MthCell ) 'MthCell is not being recognized

 

[My Aswer Is This]

I missed the part about Month Name:
Try this: It will give you a Message Box with the answer. You modify it to put the value where you want

Private Sub CommandButton1_Click()
'Modified  7/20/2022  9:50:48 PM  EDT
Dim ans As String
ans = Format(Range("D2"), "MMMM")
MsgBox ans
End Sub

 

[johnnyL]

@Derick63 Did you execute the code in post #5 and check what MthCell is equal to?

 

[Derick63]

TheMonthName = Format(YourDateValue, "mmmm")

My humblest apologies sir. I saw your reply and not wearing my glasses didn't add to it. My mistake on the reply I sent. I'm trying your suggestion as I write this. I appreciate your solution.

 

[Peter_SSs]

tried using MthCell = MonthName(Range("D2")) and the error message was "Invalid Procedure Call or Argument".

The reason that you got the error with this attempt is that you were feeding the MonthName function an argument value of (probably) 40,000 or more, being the numerical value of the complete date in D2 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))