|I've created a timesheet workbook within excel for my staff to use from one pay period to the next. Within the timesheet, staff are able to track their comp time earned/used, sick time earned/used, personal time earned/used etc... I was able to get some help with some VBA to create subsequent new timesheets within the workbook (timesheet 2, timesheet 3, etc...). I have included the VBA below.|
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Timesheet")
ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30").PasteSpecial _
This VBA creates a new timesheet within the workbook, numbers them accordingly as I indicated above and moves the staff members balances in (Sick time, Comp time, Vacation, Personal, etc..) from the "New balance" column from the previous timesheet to the "Previous Balance" column within the new timesheet. The idea is that the staff member, for each new pay period, would open the last timesheet create and create a new timesheet and that the balances for Sick, Comp, Personal, etc... would move from one timesheet to the next.
The VBA above does create a new timesheet and on the initial (1st), creation of a new timesheet works perfectly to move balances from New Balance within the last timesheet to the Previous Balance within the new timesheet. But, when creating subsequent timesheets from the last timesheet created (timesheet 2) the VBA refers back to the original timesheet and not the last created (timesheet 2, timesheet 3 timesheet 4 and so forth) as a result, is not bringing forth the new earned/used times in the Sick, Comp, Vacation, Personal, etc... losing the ability to track these times effectively. Many of the fields are blocked so that they cannot be tampered with, as a result staff members are not able to adjust them manually. I've tried to allow them to do this in the past and it was nothing but a problem.
How can I adjust the VBA above to work going forward, to not refer back to the original Timesheet and instead work with the subsequent timesheets (timesheet 2, timesheet 3, timesheet 4, etc...
Any help with this would be greatly appreciated.