Timesheet workbook VBA

[Jmoz092]

If possible, I'd like to alter a code that I've been using so that we can apply it to a macro button in the workbook for the user. I want to change it so that after the user enters the last day of the first week that will create the name of the first worksheet, then the VBA recognizes how many more weeks are remaining in the year and creates the appropriate number of sheets for the rest of the year.

i.e., if we make a new workbook for an existing employee, at the beginning of 2018, we'll enter "1-6-18" into the first input box and then the VBA will recognize that we'll need 26 worksheets (2 weeks per worksheet/pay period) and make them

but, if we hire a new employee in the middle of the year and his/her first week ends 5-19-18 (date entered into first input box when making new workbook), the VBA would recognize that we're in the 20th week of the year and that we'll only need to make 16 worksheets for this new employee.

Here's the code I was using. It was designed to duplicate the master time sheet from 8-25-17 to the end of 2017.

Sub YearWorkbookStartAtAug25()
    
    Dim sht As Variant
    Dim sTemp As String
    Dim dSDate As Date


    sTemp = InputBox("Date for the first worksheet:", "End of Week?")
    dSDate = CDate(sTemp) - 1
    
    Application.ScreenUpdating = False
    Worksheets.Add After:=Worksheets(Worksheets.Count), _
      Count:=(11 - Worksheets.Count)
    For Each sht In Worksheets
        sht.Name = Format(dSDate, "mm-dd-yy")
        dSDate = dSDate + 14
    Next sht
    Application.ScreenUpdating = True
End Sub

 

[Jmoz092]

That's fantastic. It takes out the weekends, very nice.

If I want to exclude holidays, I'll just make an array of those dates on a sheet (lets say Sheet2) and then change:

Shts = WorksheetFunction.Networkdays(dSDate, eDate)

to:

Shts = WorksheetFunction.Networkdays(dSDate, eDate, Holidays)

Is that right?
Would I need to? :

Dim Holidays as array

My attempt at the syntax of that was way off, compared to what you wrote ;(

 

[Fluff]

Try

Sub YearWorkbookStartAtAug25()
    
   Dim Cnt As Long
   Dim sTemp As String
   Dim dSDate As Date
   Dim Shts As Long
   Dim eDate As Date
   Dim Holdays As Variant
   
   sTemp = InputBox("Date for the first worksheet:", "End of Week?")
   Holdays = Application.Transpose(Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("[COLOR=#ff0000]A1:A10[/COLOR]"))
   dSDate = CDate(sTemp)
   eDate = "31/12/" & Year(dSDate)
   Shts = WorksheetFunction.NetworkDays(dSDate, eDate, Holdays)
   
   Application.ScreenUpdating = False
   For Cnt = 1 To Shts
      If WorksheetFunction.WorkDay(dSDate - 1, 1, Holdays) = dSDate Then
         Sheets("SBD").Copy after:=Sheets(Sheets.Count)
         ActiveSheet.Name = Format(dSDate, "mm-dd-yy")
         dSDate = dSDate + 1
      Else
         dSDate = dSDate + 1
         Cnt = Cnt - 1
      End If
   Next Cnt
End Sub

Change values in red to suit, but the list of holiday dates must be in 1 column

 

[Jmoz092]

Why is Holidays a variant and not an array?

Along the lines of A1:A10, can you allow for a dynamic range of dates to exclude by referencing column A on Sheet2 as:

.Range("A1:A")

Thanks Fluff.

 

[Fluff]

Holdays is an array, but is declared as Variant.
To make it dynamic use this

   With Sheets("Sheet2")
      Holdays = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)))
   End With

 

[Jmoz092]

Ugh, that xlUp. I'll get that function eventually. Thanks, as always, for the education Fluff

 

[Fluff]

Glad to help & thanks for the feedback