更新文件时如何不删除隐藏的选项卡

我有一个宏,它可以更新一系列模板,并且在推出后意识到它已经删除了源文件中所有隐藏的选项卡。我需要弄清楚如何阻止此宏删除隐藏的选项卡。

我已经梳理了这段代码一段时间,并且遇到了障碍。

非常感谢任何帮助,在此先感谢!

Sub File_Loop_Updater()
    'Excel VBA code to loop through files in a user selectable folder with Excel VBA

    'Variables for folders, target files and row selection
    Dim TargetFolder As String, TargetFile As String
    Dim wsConsol As Worksheet

    Dim i As Integer
    Dim s As Worksheet

    'Sets the name of the wsConsol file, if required
    Set wsConsol = Workbooks("UpdaterFileName.xlsm").Worksheets("Summary")

    'Opens a file dialog box for user to select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       TargetFolder = .SelectedItems(1)
       Err.Clear
    End With

    'stops screen updating, calculations, events, and status bar updates to help code run faster
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'This section will loop through and open each file in the folder you selected
    'and then close that file before opening the next file

    TargetFile = Dir(TargetFolder & "\", vbReadOnly)

    Do While TargetFile <> ""
        DoEvents
        On Error GoTo 0
        Workbooks.Open Filename:=TargetFolder & "\" & TargetFile, UpdateLinks:=False

        ''''''''''''START CODE HERE TO DO SOMETHING'''''''''
        'Unprotect workbook with a password
        ActiveWorkbook.Unprotect Password:="lease"

        'Loop through and unlock all sheets in the workbook
        For i = 1 To 2
            Sheets(i).Unprotect Password:="lease"
        Next i

        'Copy specific data cells from Summary sheet of Updater to Summary sheet of TargetFile       
        wsConsol.Activate
        Range("D31:L219").Copy
        Workbooks(TargetFile).Worksheets("Lease Liability Summary").Range("D31:L219").PasteSpecial xlPasteFormulas 'Pastevalue current data into cells
        wsConsol.Range("D31:L219").Copy
        Workbooks(TargetFile).Worksheets("Lease Liability Summary").Range("D31:L219").PasteSpecial xlPasteFormats  'Pasteformat to unlock cells
        wsConsol.Activate
        Range("B20:B20").Copy
        Workbooks(TargetFile).Worksheets("Lease Liability Summary").Range("B20:B20").PasteSpecial xlPasteValues


        'Reset wsConsol
        Set wsConsol = Workbooks("UpdaterFileName.xlsm").Worksheets("Summary")

        'Remove external links copied
        Workbooks(TargetFile).Worksheets("Lease Liability Summary").Activate
        Cells.Replace What:="[UpdaterFileName.xlsm]", _
        Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:= _
        False, SearchFormat:=False, ReplaceFormat:=False


        'Loop through and lock all sheets in the workbook
        For i = 1 To 2
            Sheets(i).Protect Password:="lease"
        Next i

        'Protect workbook with a password
        ActiveWorkbook.Protect Password:="lease"

        ''''''''''''END CODE HERE THAT DID SOMETHING'''''''''

        'Close TargetFile Workbook
        Application.Calculation = xlAutomatic
        Workbooks(TargetFile).Close SaveChanges:=True
        Application.Calculation = xlCalculationManual
        TargetFile = Dir
    Loop

    'turns settings back on that you turned off before looping folders
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomatic

End Sub
评论