Monthly workbook with daily worksheets...Help please

ikkin

New Member
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

*First timer here*

I'm hoping someone can help - i may be overthinking things so apologies in advance.

I use a monthly workbook with worksheets for days of the month to capture transport usage data... so 30-31 sheets in each workbook for every month.

Is there a way to simplify this process without having to manually rename the worksheets and enter the sequential date formulas onto each worksheet as a heading?

To push my luck further is there a way to create a template of each monthly workbook where i can then update the year (for the dates) to roll on annually?

Any advice would be greatly appreciated.

Many thanks
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
hi ikkin,

Welcome to the board.

If all your worksheets are exactly the same in structure, why dont you consolidate all of them in a single worksheet and then use autofilter to extract related data???

hth...
 

ikkin

New Member
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
hi ikkin,

Welcome to the board.

If all your worksheets are exactly the same in structure, why dont you consolidate all of them in a single worksheet and then use autofilter to extract related data???

hth...
Thank you so much for your reply - again i will apologise in advance. The daily worksheets in the Monthly workbook have data entered daily (sometimes days / weeks in advance) so its not the extraction of any data i need but more to streamline the process for creating the monthly workbooks (x 12 months) with daily worksheets ( 30 - 31 days respectively) on a yearly basis. Does that make more sense / any sense? I promise i am not as docile as i am certainly coming across as :)
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
:) its not like you are docile or anything, it was just an hoest suggestion, because i do it myself, and besides
creating the monthly workbooks (x 12 months) with daily worksheets ( 30 - 31 days respectively) on a yearly basis.
You can do just that 👆👆👆👆with much more ease by just creating a single file for daily data entry.

Anyway, so if i get it right, then creating 12x workbooks, each having worksheets representing number of days in a month is a prerequisite to your task?? And instead of creating a worksheet every day you want to create all 12x workbooks in a single go?? Is that what is needed???

I m sorry if i m not getting your point but this is what i understood from
streamline the process for creating the monthly workbooks (x 12 months) with daily worksheets ( 30 - 31 days respectively) on a yearly basis
 

ikkin

New Member
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

:) its not like you are docile or anything, it was just an hoest suggestion, because i do it myself, and besides

You can do just that 👆👆👆👆with much more ease by just creating a single file for daily data entry.

Anyway, so if i get it right, then creating 12x workbooks, each having worksheets representing number of days in a month is a prerequisite to your task?? And instead of creating a worksheet every day you want to create all 12x workbooks in a single go?? Is that what is needed???

I m sorry if i m not getting your point but this is what i understood from
 

ikkin

New Member
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks so much for your fast replies. If there is a way I can do as you suggested above using a single file, I’m very intrigued to learn it. Yes, you understood correctly. it’s me that cannot seem to articulate it very well. I’m sorry. I’ll try to upload a screenshot of one used last year. - 5619E18E-5E3C-42D1-B8A3-F218424A925A.png
sorry - currently on my phone. Is that any clearer? Hahaha god I’m useless!!
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
For
To push my luck further is there a way to create a template of each monthly workbook where i can then update the year (for the dates) to roll on annually?

1st Approach:
Your initial query.
try following code
VBA Code:
Sub master_template()

lr = ActiveWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lc = ActiveWorkbook.Sheets("sheet1").Cells(4, Columns.Count).End(xlToLeft).Column

mymon = Range("n1")
myyear = Range("n2")

srtdate = CDate("1/" & mymon & "/" & myyear)
endate = CDate(WorksheetFunction.EoMonth(srtdate, 0))
Sheets("sheet1").Range(Cells(1, 1), Cells(lr, lc)).Copy

Workbooks.Add
With ActiveWorkbook
    .Sheets("sheet1").Name = 1
    .Sheets(1).Range("a1").PasteSpecial Paste:=xlPasteValues
    .Sheets(1).Range("a1").PasteSpecial Paste:=xlPasteFormats
    .Sheets(1).Range("a1") = Format(srtdate, "dddd dd mmmm yyyy")


For i = 2 To (endate - srtdate + 1)
    .Worksheets.Add after:=Sheets(Sheets.Count)
    .Worksheets(Sheets.Count).Name = i
    .Sheets(i).Range("a1").PasteSpecial Paste:=xlPasteValues
    .Sheets(i).Range("a1").PasteSpecial Paste:=xlPasteFormats
    .Sheets(i).Range("a1") = Format(CDate(i & "/" & mymon & "/" & myyear), "dddd dd mmmm yyyy")
Next i

Application.CutCopyMode = False
End With

strdate = ""
endate = ""
End Sub

Explanation:
Lets assume you have your Blank template ready in Sheet 1 of workbook "Master Template". Sheet 1 also contains two validations lists in O2 and O3, for months and years respectively.
Now for the code to run, you have to select month and year and press Create Workbook button. The code then creates a new workbook, for selected month containing separate worksheets for each day, copying your template and placing your headers to each worksheet in the process. Reference file is here.


2nd Approach:
The way I would like to approach this query is by having a master data file containing all the data and then create workbooks with desired data from that master data file. I like this approach because it allows me to use pivot tables to summarize and create number of reports on various basis.

To accomplish this task this way, try the following code:

VBA Code:
Sub Consolidated_file()

Dim rng1 As Range
Dim rng2 As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

lr = ActiveWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lc = ActiveWorkbook.Sheets("sheet1").Cells(4, Columns.Count).End(xlToLeft).Column

mymon = Range("o1")
myyear = Range("o2")
crisrt = CDate("1/" & mymon & "/" & myyear)
criend = CDate(Day(WorksheetFunction.EoMonth(crisrt, 0)) & "/" & mymon & "/" & myyear)

Workbooks.Add
Workbooks(1).Activate
   
For i = crisrt To criend
   
    Workbooks(1).Sheets("sheet1").Range("a4:j4").AutoFilter
    Workbooks(1).Sheets("sheet1").Range("a4:j4").AutoFilter field:=1, Criteria1:=Format(i, "dd/mm/yy")
   
    On Error Resume Next
    Workbooks(1).Activate
    visblecellscount = Workbooks(1).Sheets("sheet1").Range(Cells(5, 1), Cells(lr, 1)).SpecialCells(xlCellTypeVisible).Count
   
    If Not visblecellscount = "" Then
        On Error Resume Next
        Set rng1 = Workbooks(1).Sheets("sheet1").Range(Cells(1, 2), Cells(4, 10))
        Set rng2 = Workbooks(1).Sheets("sheet1").Range(Cells(5, 2), Cells(lr, 10)).SpecialCells(xlCellTypeVisible)
       
        s = Workbooks(2).Sheets.Count
       
        Workbooks(2).Activate
        Workbooks(2).Sheets.Add(After:=Sheets(s)).Name = Left(i, 2)
       
        rng1.Copy
        Workbooks(2).Sheets(Left(i, 2)).Range("a1").PasteSpecial Paste:=xlPasteValues
        Workbooks(2).Sheets(Left(i, 2)).Range("a1").PasteSpecial Paste:=xlPasteFormats
        Workbooks(2).Sheets(Left(i, 2)).Range("a1") = Format(i, "dddd dd mmmm yyyy")

        rng2.Copy
        Workbooks(2).Sheets(Left(i, 2)).Range("a5").PasteSpecial Paste:=xlPasteValues
        Workbooks(2).Sheets(Left(i, 2)).Range("a5").PasteSpecial Paste:=xlPasteFormats
    End If
   
    visblecellscount = ""
Next i

    Workbooks(2).Sheets(1).Delete
    Workbooks(1).Sheets("sheet1").Range("a4:j4").AutoFilter

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Explanation:
The structure of this file is an exact copy of the previous file except, we have dates in Column A. Selection of month and year is similar, but the functionality is a bit different. The code goes through your master data file and looks for data corresponding to the month and year selected. then creates a new workbook with corresponding data found in master data file. Reference file is here.

Difference:
Although the criteria for each of the above codes is the same (create daily worksheets for desired month), basic difference is, Master Template creates worksheets for each day, while Consolidated File creates worksheets only for days found in your master data.


Use whichever seems good to you. :)

hth....

P.s. When trying out reference files, execute one file at a time, as both files use workbook indexes.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,355
Messages
5,624,207
Members
416,017
Latest member
moritz210

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
Top