Solved How can I used VBA to create a new timesheet?

April 19, 2016 at 06:58:56
Specs: Windows 7
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.
Sub Test()
ActiveSheet.Unprotect Password:="password"
Dim ws1 As Worksheet
  Set ws1 = ThisWorkbook.Worksheets("Timesheet")
    ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
       ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30").PasteSpecial _
ActiveSheet.Protect Password:="password"

End Sub

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.

See More: How can I used VBA to create a new timesheet?

April 19, 2016 at 08:03:32
✔ Best Answer
Assuming that the macro is being run from the latest copy of the timesheet each time, try replacing this line:

Set ws1 = ThisWorkbook.Worksheets("Timesheet")

with this:

Set ws1 = ThisWorkbook.Worksheets(ActiveSheet.Name)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 19, 2016 at 08:31:01
Thank you so much! I tested creating several new timesheets and it works perfectly. Thanks again!

Report •
Related Solutions

Ask Question