Automatically make 52 sheets with correct date

MaryPoppins

New Member
Joined
Nov 22, 2016
Messages
13
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

Image%202018-01-05%20at%202.04.39%20pm.png
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Select the sheet you want to save as a PDF file and run this macro. Change the folder path to suit your needs.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top