Get month name from date to use as Worksheet name

Derick63

Board Regular
Joined
Apr 6, 2018
Messages
71
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.
VBA Code:
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
 
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.
VBA Code:
    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
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You cant Set wsMth = Worksheets(MthCell) If that sheetname doesn't exist yet. ;)
 
Upvote 0
wsMth already is a worksheet so
Sheets(wsMth).Range("C8:C ...
wsMth.Range("C8:C ...
 
Upvote 0
Solution
.....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
VBA Code:
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?
 
Upvote 0
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 👍
 
Upvote 0
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. 👍
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top