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

 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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
 

MaryPoppins

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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
 

Forum statistics

Threads
1,081,578
Messages
5,359,740
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top