Monthly workbook with daily worksheets...Help please

[ikkin]

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

 

[fadee2]

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]

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]

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]

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]

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. -
sorry - currently on my phone. Is that any clearer? Hahaha god I’m useless!!

 

[fadee2]

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

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:

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.