Automatically make 52 sheets with correct date

[MaryPoppins]

Hi, I'd like some advice on improving my timesheet file I use for work. At the moment, I have a seperate excel file for each month with 4 x weekly timesheets as seperate worksheets.

I'd like to condense this into one big file, with 52 sheets in, one for each week. I'd like each sheet to be named with the week commencing date, which will be cell C4. That is the only cell I would like to change on each of the 52 sheets, then I will fill everything else out manually each week. Is there a simple way of automating this so I have a timesheet page for each week of the year setup ready to fill out.

I've tried explaining best I can but if there's anything I've missed let me know.

Thanks,
Poppins

 

[mumps]

I am assuming that the format of the sheet you posted will be the same for all weekly sheets. The macro will create copies of your existing template sheet for each week of the year based on the date you currently have in C4.

Sub createSheets()
    Application.ScreenUpdating = False
    Dim x As Long, y As Long
    y = 7
    For x = 1 To 52
        Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
        Range("C4") = Sheets("Sheet1").Range("C4") + y
        ActiveSheet.Name = Replace(Range("C4"), "/", "-")
        y = y + 7
    Next x
    Application.ScreenUpdating = True
End Sub

 

[MaryPoppins]

Wow! Thank you so much, I didn't think it would be so simple!

 

[MaryPoppins]

Is there a way I could write a macros to click file > print > safe to pdf > 01/01/2018 Timesheet.pdf?

Would I use a macros to do this sort of thing?

 

[My Aswer Is This]

Just a piece of advice if you don't mine.

The less you split out all your data into different Workbooks and different sheets the easier it is when you want to see yearly monthly or daily results.

If you have all your yearly data on one sheet with the proper formulas you can get daily monthly and yearly data easily.

For example if your boss wants to know how many Apples we sold this year and also for each month of the year or even how many we sold on April 22 if all this data is in the same sheet a formula could get you those results very easily. But if all your data is spread out over 52 different sheets it will make things much harder.

For example I have all my finances in one sheet and with just a change to two cells on my sheet I can see how much I spent in one year for Apples. Or how much I spent for Apples in one particular Month or a certain range like from April 1 to June 25

Just a thought.

 

[mumps]

Select the sheet you want to save as a PDF file and run this macro. Change the folder path to suit your needs.

Sub SavePDF()
    ChDir "C:\Test\" 'change the folder path to suit your needs
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub