How to create multiple Workbooks from a Template for every day of year?

CassandraB

New Member
Joined
Jan 18, 2019
Messages
1
I use the same template for a cash-out every day of the year. I'd like to create a new workbook series that contains the Date. Preferably these workbooks would be organized in folders by Month.

Grateful for your assistance!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
The macro will create 365 books, and 12 folders named "Jan", "Feb", "Mar", etc., in each folder will put the files corresponding to the month.
Each file will be named as "cash-out " and date, e.g "cash-out 01-01-2019.xlsx"

Put the macro in the book that contains the template sheet, change "Template" by the name of your sheet.

Code:
Sub Create_Multiple_Workbooks()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = False
    '
    Dim fec1 As Date, fec2 As Date
    Dim l1 As Workbook, h1 As Worksheet
    Dim ruta As String
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets("Template")      'name of template sheet
    '
    ruta = l1.Path & "\"
    fec1 = DateSerial(Year(Date), 1, 1)
    fec2 = DateSerial(Year(Date), 12, 31)
    For i = fec1 To fec2
        Application.StatusBar = "Creating file : " & i
        mes = Format(i, "mmm")
        If Dir(ruta & mes & "\") = "" Then
            MkDir (ruta & mes)
        End If
        ruta2 = ruta & mes & "\"
        arch = "cash-out " & Format(i, "mm-dd-yyyy")
        h1.Copy
        Set l2 = ActiveWorkbook
        l2.SaveAs Filename:=ruta2 & arch & ".xlsx", _
            FileFormat:=xlOpenXMLWorkbook
        l2.Close False
    Next
    Application.StatusBar = False
    MsgBox "End"
End Sub

Let me know if you have any question.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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