更新年度报告模板，最好在下一年开始之前

Question

工作流程是：

• 复制前一年报告文件夹
• 更新以下模板
• 保存
• 关

源文件夹和NewYear文件夹中有19个工作簿。每个工作簿都必须保存年底的最终数据( "YTD实际“页面)，以及重置和准备新年的月表。

不要害怕骂我或冒犯我。我对此显然是新手，必须有更好的方法来做。

Private Sub Workbook_Open()

Dim CYR As Variant, PYR As Integer, InputError As Integer, SourceFolder As String, NewYearFolder As String, NewYearFiles As String, oFSO As Object, oNewYearFolder As Object, oNewYearFiles As Object, filename As Variant

CYR = InputBox("Input New Year in 4 Digit Format. XXXX")
    On Error GoTo ErrorCheck

PYR = CYR - 1
SourceFolder = "C:\Users\nick.hasler\Desktop\Daily Service Reporting\" & PYR & " Service Report - Daily"
NewYearFolder = "C:\Users\nick.hasler\Desktop\Daily Service Reporting\" & CYR & " Service Report - Daily"
NewYearFiles = Dir(NewYearFolder & "\" & "*.xl??")

Application.ScreenUpdating = False
Application.EnableEvents = False

If Dir(NewYearFolder, vbDirectory) = "" Then

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.copyFolder SourceFolder, NewYearFolder

    MsgBox "New Year Folder Created"

    Set oNewYearFolder = oFSO.GetFolder(NewYearFolder)
    Set oNewYearFiles = oNewYearFolder.Files

    For Each filename In oNewYearFiles
        If filename Like "*" & PYR & "*" Then
          NewFileName = Replace(filename, PYR, CYR)

          Name filename As NewFileName
        End If
    Next filename

    MsgBox "New Year Files Renamed"
    MsgBox "The Next Step Will Take a Few Moments"

    NewYearFiles = Dir(NewYearFolder & "\" & "*.xl??")

    Do While NewYearFiles <> ""
        Workbooks.Open (NewYearFolder & "\" & NewYearFiles)

        Workbooks(NewYearFiles).Sheets(PYR & " YTD").Select
        Workbooks(NewYearFiles).Sheets(PYR & " YTD").Name = CYR & " YTD"
        Workbooks(NewYearFiles).Worksheets("2019 Actual").Select
        Workbooks(NewYearFiles).Worksheets(PYR & " Actual").Copy Before:=Workbooks(NewYearFiles).Worksheets(CYR & " YTD")
        Workbooks(NewYearFiles).Worksheets(PYR & " Actual (2)").Select
        Workbooks(NewYearFiles).Worksheets(PYR & " Actual (2)").Name = CYR & " Actual"
        Workbooks(NewYearFiles).Worksheets(PYR & " Actual").Select
        Workbooks(NewYearFiles).Worksheets(PYR & " Actual").Cells.Select
        Selection.Copy
        Workbooks(NewYearFiles).Worksheets(PYR & " Actual").Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        'Monthly Reporting
        Workbooks("Create New Year").Worksheets("Monthly Reporting").Range("A5:AH16").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("Monthly Reporting").Range("A5:AH16")

        Workbooks("Create New Year").Worksheets("Monthly Reporting").Range("H76:AH88").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("Monthly Reporting").Range("H76:AH88")

            Workbooks(NewYearFiles).Worksheets("Monthly Reporting").Range("A1:AH90").Replace What:="qqq", Replacement:="="

            Workbooks(NewYearFiles).Worksheets("Monthly Reporting").Range("A1:J4").Replace What:=PYR, Replacement:=CYR

        'Daily Reporting
        Workbooks("Create New Year").Worksheets("Daily Reporting").Range("A2:AG18").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("Daily Reporting").Range("A2:AG18")

            Workbooks(NewYearFiles).Worksheets("Daily Reporting").Range("A1:AG18").Replace What:="qqq", Replacement:="="

            Workbooks(NewYearFiles).Worksheets("Daily Reporting").Range("A3:AG3").Replace What:="Year", Replacement:=CYR

        'January
        Workbooks("Create New Year").Worksheets("January").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("January").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("January").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("January").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("January").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'February
        Workbooks("Create New Year").Worksheets("February").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("February").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("February").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("February").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("February").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'March
        Workbooks("Create New Year").Worksheets("March").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("March").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("March").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("March").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("March").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'April
        Workbooks("Create New Year").Worksheets("April").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("April").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("April").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("April").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("April").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'May
        Workbooks("Create New Year").Worksheets("May").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("May").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("May").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("May").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("May").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'June
        Workbooks("Create New Year").Worksheets("June").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("June").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("June").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("June").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("June").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'July
        Workbooks("Create New Year").Worksheets("July").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("July").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("July").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("July").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("July").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'August
        Workbooks("Create New Year").Worksheets("August").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("August").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("August").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("August").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("August").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'September
        Workbooks("Create New Year").Worksheets("September").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("September").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("September").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("September").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("September").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'October
        Workbooks("Create New Year").Worksheets("October").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("October").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("October").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("October").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("October").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'November
        Workbooks("Create New Year").Worksheets("November").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("November").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("November").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("November").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("November").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        'December
        Workbooks("Create New Year").Worksheets("December").Range("A7:AFH46").Copy _
            Destination:=Workbooks(NewYearFiles).Worksheets("December").Range("A7:AFH46")

            Workbooks(NewYearFiles).Worksheets("December").Range("A1:AFH7").Replace What:=PYR, Replacement:=CYR

            Workbooks(NewYearFiles).Worksheets("December").Range("ADZ7:AFH7").Replace What:="PYear", Replacement:=PYR

            Workbooks(NewYearFiles).Worksheets("December").Range("A2:AFH46").Replace What:="qqq", Replacement:="="

        Application.CutCopyMode = False

        Workbooks(NewYearFiles).Save
        Workbooks(NewYearFiles).Close
        NewYearFiles = Dir()
    Loop

    MsgBox "New Year Files Reset"

Else
    InputError = 1
End If


ErrorCheck:
If CYR = "" Then
    MsgBox "You did not input a valid year"
End If

If InputError = 1 Then
    MsgBox "That Year Already Exist. Delete the folder if you wish to replace it."
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

'Workbooks("Create New Year").Save
'Workbooks("Create New Year").Close

End Sub

Answer 1