Copy Excel workbook for each day of the month, changing date in name and template

Question

I am looking to automate a current process using VBA.
For every day of each month, I need to copy an existing Excel template into a new workbook, only changing the date in merged cells C3-D3, as well as in the name of the file. The filename should always be similar to: "ACF_31 03 2018_XYZ". I have so far found and adapted the code below which, when run once, creates a drop down button with the month names. It would be great to save all the files for a respective month within their own folder.

Sub RunOnce()
'Run this code once to set up the worksheet button
ActiveSheet.DropDowns.Add(0, 2, 100, 15).Name = "cbMonth"
Worksheets("Sheet1").Shapes("cbMonth").ControlFormat.List = _
Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
ActiveSheet.Shapes("cbMonth").ControlFormat.DropDownLines = 12
ActiveSheet.Buttons.Add(105, 2, 120, 15).Select
    With Selection
        .Name = "Create Folder Structure"
        .OnAction = "CreateFolders"
        .Characters.Text = "Create Folder Structure"
        .Font.Color = RGB(0, 100, 200)
        .Font.Size = 11
    End With
Range("G1").Select

End Sub

Many thanks.

This code has nothing to do with what would you like to achieve. I don't know why do you create drop-down list while you need just to copy sheet to the new workbook and save it. — MarcinSzaleniec, Feb 26 '18 at 09:10
@MarcinSzaleniec - I did that so that other colleagues can also easily do this process at the end of each month, so that they can just select the next month, and then click the button to create workbooks for every day, e.g. 31 workbooks for March, each containing the respective date in both filename and inside the template. I will post some examples of what I have been trying so far as well. — julianz03, Feb 26 '18 at 09:27
Not the best coding in the world but I wrote a shell that generates folders by month and populates with current file saved as a new document for each day of the respective month. Might be able to use some of it? https://stackoverflow.com/questions/48964106/i-want-to-create-a-vba-code-in-word-that-will-create-multiple-word-files-with-di/48964249#48964249 — QHarr, Feb 26 '18 at 11:26

score 0 · Answer 1 · answered Feb 26 '18 at 12:03

you could use such a sub (explanations in comments):

Sub CreateFolders()
    Dim name As String
    Dim iDay As Long
    Dim iMonth As Long

    iMonth = ActiveSheet.Shapes("cbMonth").ControlFormat.ListIndex ' get currently listbox index

    For iDay = 1 To Day(DateSerial(Year(Date), iMonth + 1, 0)) 'loop between 1 and currently selected month last day
        name = "ACF_" & Format(DateSerial(Year(Date), iMonth, iDay), "dd mm yyyy") & "_XYZ" ' build the current "name"
        With Workbooks.Add("C:\....\MyTemplate.xlsx") ' open and reference a new workbook based on a template (change "C:\....\MyTemplate.xlsx" to full path of your actual template file)
            .Sheets(1).Range("C3").Value = name ' write the "name" into range "C3" of referenced workbook 1st sheet
            .SaveAs name 'save referenced workbook with given "name"
            .Close True ' save and close referenced workbook
        End With
    Next
End Sub

Many thanks, this works very well and is exactly what I needed. This will save me a lot of repetitive work and has certainly determined me to learn VBA. — julianz03, Feb 26 '18 at 13:42

Copy Excel workbook for each day of the month, changing date in name and template

1 Answers1