Help with macro automation

[stolenweasel]

Hello I can't find anything that quite fits what I'm looking for on this project. I have a workbook which tracks Aircraft airspace and site usage. The workbook contains 31 sheets (days) of data that is input by multiple employees. Each sheet has formulas to total up data in multiple fields. The last sheet is all formulas for monthly totals.

I would like to have a macro that takes one of these workbooks and spits out 12 identical workbooks labeled for the month and year. This is a fiscal year system, so I would put these in October through September order in a folder labeled 2025. Each sheet also needs to be write protected be cause we have some folks that can't help themselves of inadvertently erasing formulas and resizing boxes, etc. Thank you for your help. This is a few steps above my level.

Template - there should.be a macro in here to protect/unprotect all sheets

Dropbox

www.dropbox.com

R/S
Jeff George

 

[mumps]

The first macro which creates the 12 workbooks, assumes that all sheets are already protected. The macro will prompt you to enter the year. The file names for the months of October, November and December will use the entered year while the rest of the months will use the following year. Change the password (in red) and the folder path (in blue) to suit your needs.

Sub CreateWorkbooks()
    Application.ScreenUpdating = False
    Dim x As Long, mName As String, sYear As String, myPath As String
    sYear = InputBox("Please enter the year.")
    If sYear = "" Then Exit Sub
    myPath = "C:\Test\"
    For x = 1 To 12
        mName = MonthName(x, False)
        Select Case mName
            Case "October", "November", "December"
                ActiveWorkbook.SaveCopyAs Filename:=myPath & mName & "-" & sYear & ".xlsm"
            Case Else
                ActiveWorkbook.SaveCopyAs Filename:=myPath & mName & "-" & sYear + 1 & ".xlsm"
        End Select
    Next x
    Application.ScreenUpdating = True
End Sub

Sub ProtectSheets()
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Protect Password:="MyPassword"
    Next ws
End Sub

Sub UnProtectSheets()
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Unprotect "MyPassword"
    Next ws
End Sub

 

[stolenweasel]

The first macro which creates the 12 workbooks, assumes that all sheets are already protected. The macro will prompt you to enter the year. The file names for the months of October, November and December will use the entered year while the rest of the months will use the following year. Change the password (in red) and the folder path (in blue) to suit your needs.

Sub CreateWorkbooks()
    Application.ScreenUpdating = False
    Dim x As Long, mName As String, sYear As String, myPath As String
    sYear = InputBox("Please enter the year.")
    If sYear = "" Then Exit Sub
    myPath = "C:\Test\"
    For x = 1 To 12
        mName = MonthName(x, False)
        Select Case mName
            Case "October", "November", "December"
                ActiveWorkbook.SaveCopyAs Filename:=myPath & mName & "-" & sYear & ".xlsm"
            Case Else
                ActiveWorkbook.SaveCopyAs Filename:=myPath & mName & "-" & sYear + 1 & ".xlsm"
        End Select
    Next x
    Application.ScreenUpdating = True
End Sub

Sub ProtectSheets()
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Protect Password:="MyPassword"
    Next ws
End Sub

Sub UnProtectSheets()
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Unprotect "MyPassword"
    Next ws
End Sub

Thank you I will give it a shot when I get to work tonight and let you know. I appreciate it

 

[stolenweasel]

The first macro which creates the 12 workbooks, assumes that all sheets are already protected. The macro will prompt you to enter the year. The file names for the months of October, November and December will use the entered year while the rest of the months will use the following year. Change the password (in red) and the folder path (in blue) to suit your needs.

Sub CreateWorkbooks()
    Application.ScreenUpdating = False
    Dim x As Long, mName As String, sYear As String, myPath As String
    sYear = InputBox("Please enter the year.")
    If sYear = "" Then Exit Sub
    myPath = "C:\Test\"
    For x = 1 To 12
        mName = MonthName(x, False)
        Select Case mName
            Case "October", "November", "December"
                ActiveWorkbook.SaveCopyAs Filename:=myPath & mName & "-" & sYear & ".xlsm"
            Case Else
                ActiveWorkbook.SaveCopyAs Filename:=myPath & mName & "-" & sYear + 1 & ".xlsm"
        End Select
    Next x
    Application.ScreenUpdating = True
End Sub

Sub ProtectSheets()
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Protect Password:="MyPassword"
    Next ws
End Sub

Sub UnProtectSheets()
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Unprotect "MyPassword"
    Next ws
End Sub

It appears to work great is is there are a couple things that could help if it will work. The Fiscal year thing doesn't seem to work right as in I have to put in the previous year in the date picker In order for the names to come out correct. For example In order to get FY 2025 I have to enter 2024 in the box. Otherwise will just put 2025 and 2026. If that's not fixable then it's workable like it is. The other thing is the names of the workbooks themselves. Is there away to have it output them a little different. Currently we have the folder so it displays them in order. To achieve that we name the files like this 1 Oct 2024 then the next would be 2 Nov 2024. Jan 2025 would be 4 Jan 2025. These are really nice to haves because it's not much to just rename each one after the macro is run.
I Really do appreciate the the quick help. Thank You

R/S
Jeff George

 

[stolenweasel]

It appears to work great is is there are a couple things that could help if it will work. The Fiscal year thing doesn't seem to work right as in I have to put in the previous year in the date picker In order for the names to come out correct. For example In order to get FY 2025 I have to enter 2024 in the box. Otherwise will just put 2025 and 2026. If that's not fixable then it's workable like it is. The other thing is the names of the workbooks themselves. Is there away to have it output them a little different. Currently we have the folder so it displays them in order. To achieve that we name the files like this 1 Oct 2024 then the next would be 2 Nov 2024. Jan 2025 would be 4 Jan 2025. These are really nice to haves because it's not much to just rename each one after the macro is run.
I Really do appreciate the the quick help. Thank You

R/S
Jeff George

Sorry just noticed I messed up. The naming has to be 01 Oct 2024 this way when the 10th month comes it won't sort them together.

 

[mumps]

Try:

Sub CreateWorkbooks()
    Application.ScreenUpdating = False
    Dim x As Long, mName As String, sYear As String, myPath As String
    sYear = InputBox("Please enter the year.")
    If sYear = "" Then Exit Sub
    myPath = "C:\Test\"
    For x = 1 To 12
        mName = MonthName(x, False)
        Select Case mName
            Case "October", "November", "December"
                ActiveWorkbook.SaveCopyAs Filename:=myPath & x & " " & mName & "-" & sYear - 1 & ".xlsm"
            Case Else
                ActiveWorkbook.SaveCopyAs Filename:=myPath & "0" & x & " " & mName & "-" & sYear & ".xlsm"
        End Select
    Next x
    Application.ScreenUpdating = True
End Sub

 

[stolenweasel]

Try:

Sub CreateWorkbooks()
    Application.ScreenUpdating = False
    Dim x As Long, mName As String, sYear As String, myPath As String
    sYear = InputBox("Please enter the year.")
    If sYear = "" Then Exit Sub
    myPath = "C:\Test\"
    For x = 1 To 12
        mName = MonthName(x, False)
        Select Case mName
            Case "October", "November", "December"
                ActiveWorkbook.SaveCopyAs Filename:=myPath & x & " " & mName & "-" & sYear - 1 & ".xlsm"
            Case Else
                ActiveWorkbook.SaveCopyAs Filename:=myPath & "0" & x & " " & mName & "-" & sYear & ".xlsm"
        End Select
    Next x
    Application.ScreenUpdating = True
End Sub

Thank you. Is it possible to also pull a sheet from each workbook in order to create another workbook that calculates annual totals? I was thinking about trying to use access for this but I haven't used access since like 2010.

 

[stolenweasel]

Thank you. Is it possible to also pull a sheet from each workbook in order to create another workbook that calculates annual totals? I was thinking about trying to use access for this but I haven't used access since like 2010.

The second macro works great, the naming format is much closer and it solved the year issue. All i have to do is rename them so that October is 01, November 02 and so on. Probably have to do that manually. I appreciate the help.

When you get a chance let me know what you think about my access/workbook question for annual totals.

 

[mumps]

You currently have a Monthly Totals sheet in each file. Do you want to calculate annual totals from each of those sheets? If so, would the format of the annual totals sheet in the new workbook be the same as that in the 12 files? The Monthly Totals sheet is your file is protected so I couldn't access the cells to see how it is being updated. Could you post the password?

All i have to do is rename them so that October is 01, November 02 and so on

The macro should be doing this for you.

 

[stolenweasel]

You currently have a Monthly Totals sheet in each file. Do you want to calculate annual totals from each of those sheets? If so, would the format of the annual totals sheet in the new workbook be the same as that in the 12 files? The Monthly Totals sheet is your file is protected so I couldn't access the cells to see how it is being updated. Could you post the password?

The macro should be doing this for you.

Yes the monthly totals sheet is pretty much the same as the daily sheets. Inhave a start and end sheet and the monthly totals uses the start and end sum feature for each cell. So ideally a workbook that is setup exactly the same as the monthly ones that has an annual sheet already in it, then the 12 monthly total sheets get pulled in with the data it has. Then the annual sheet could use the same method to give the annual totals. The only thing I wonder is since the data in the monthly total sheets is dependent upon the formulas when it's extracted would it loose the data?