Auto Populate Sheets?

[ShankedS]

I have an Excel spreadsheet at work that is used to track my hours. However, the sheet given to us only covers one pay period at a time. So I'm trying to make one Excel document with sheets for each pay period. I did this last year as well, but it was very tedious to go through and do 26 sheets for 26 pay periods.



I was wondering if there was a way to auto-populate sheets so that I could get those 26 sheets with a few clicks, as opposed to copying, fixing data, copying, and fixing data.

[NoSparks]

Can you post a workbook with the template sheet and tell us what the first pay period should be ?

[ShankedS]

Sure. Here.

[NoSparks]

See if this works for you...
Removed passwords.
Locked date cells.
Sheets are protected without password.

[ShankedS]

That's neat. I'm assuming you used VBA code? I'm going to need to make some modifications to get it working properly for my purposes (the state financial year, and, thus, our year, is from July to June.) Is there a function I can look up to find the code, or?

[Pecoflyer]

@Shanked
Please don't quote entire posts unnecessarily. They make threads hard to read.
Thanks

[NoSparks]

Alt + F11 brings up the VBA environment.
All code is in Module1.

[ShankedS]

Any particular reason you set a specific year range? (just curious)

[NoSparks]

specific year range...
2018 because that's where I got the 'beginning of time' date of Jan 6, 2018 which would have been the first pay period ending in calendar 2018.
2025 is just arbitrary to prevent something like 9999 being entered and Excel taking an eternity or crashing dealing with all the pay periods 'til then.

the state financial year, and, thus, our year, is from July to June

so July 2019 is the first month in the 2019 financial year ?

Mind if I ask why an .xls file when your profile shows Office 2010 ?

[ShankedS]

@NoSparks

Hmm. Well, technically I have Office 365 at home and Office 2007 at work, so I'm not sure why I set it to Office 2010.

yr = Application.InputBox("Enter 4 digit year from 2018 to 2025", "Create Sheets For Entire Year", Year(Date + 365), , , , , 1)

So what's going on here, exactly? You mention you set the date to January 6th, 2018, but changed that to:

endDate = DateSerial(2018, 7, 7)  'end of first pay period for 2019: 7/07/2018

However, it still goes Jan -> December. Though now that I look at it, that sets the endDate, not startDate. So I'm still not sure how startDate is set.

[NoSparks]

I'll adjust and fully comment the code, just tell me which months are in the 2019 financial year.
July 2018 thru June 2019 or July 2019 thru June 2020 ?

[ShankedS]

@NoSparks

The first would be correct

[NoSparks]

This should do it

When replying, just click Post Reply button instead of Quote button.

[Pecoflyer]

Unnecessary quotes have been removed - Previous version of this post deleted