Macro to Bring Tab from Another File

Lexrt999 

To run the macro without having to manually open the master file, you can create a separate Excel file to store and run your VBA code. This separate file will be your "controller" workbook. Here's a step-by-step guide on how to achieve this:

Step-by-Step Guide

1. Create a New Excel File for the Macro

• Open a new Excel workbook.
• Save it as MacroController.xlsm (make sure to save it as a macro-enabled workbook).

2. Access the VBA Editor

• Press Alt + F11 to open the VBA editor.
• Go to Insert > Module to create a new module.

3. Copy and Paste the VBA Code

• Copy the following VBA code into the new module:

Vba Code is untested backup your file

Sub UpdateMasterFile()
    Dim masterWorkbook As Workbook
    Dim sourceWorkbook As Workbook
    Dim sourcePath As String
    Dim sourceFile As String
    Dim tabNames As Variant
    Dim i As Integer
    Dim found As Boolean
    
    ' Define the path to the master workbook and the source files
    Dim masterFilePath As String
    masterFilePath = "C:\Path\To\Your\Master\File\MasterFile.xlsx" ' Change this to your master file path
    sourcePath = "C:\Path\To\Your\Source\Files\" ' Change this to your source files path
    
    ' List of tab names to be copied
    tabNames = Array("111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", "124", "125", "126", "127", "128", "129")
    
    ' Open the master workbook
    Set masterWorkbook = Workbooks.Open(masterFilePath)
    
    ' Delete existing tabs in master workbook
    On Error Resume Next
    For i = LBound(tabNames) To UBound(tabNames)
        Application.DisplayAlerts = False
        masterWorkbook.Sheets(tabNames(i)).Delete
        Application.DisplayAlerts = True
    Next i
    On Error GoTo 0
    
    ' Loop through each file in the source folder
    sourceFile = Dir(sourcePath & "*.xls*")
    Do While sourceFile <> ""
        ' Open the source workbook
        Set sourceWorkbook = Workbooks.Open(sourcePath & sourceFile)
        
        ' Copy each specified tab to the master workbook
        For i = LBound(tabNames) To UBound(tabNames)
            found = False
            On Error Resume Next
            If Not sourceWorkbook.Sheets(tabNames(i)) Is Nothing Then
                sourceWorkbook.Sheets(tabNames(i)).Copy After:=masterWorkbook.Sheets(masterWorkbook.Sheets.Count)
                found = True
            End If
            On Error GoTo 0
            If found Then Exit For ' Exit loop if tab is found and copied
        Next i
        
        ' Close the source workbook without saving
        sourceWorkbook.Close False
        
        ' Get the next file in the directory
        sourceFile = Dir
    Loop
    
    ' Save and close the master workbook
    masterWorkbook.Close SaveChanges:=True
    
    MsgBox "Tabs have been updated successfully.", vbInformation
End Sub

4. Adjust File Paths

• Update masterFilePath with the path to your master file.
• Update sourcePath with the path to your source files.

5. Running the Macro

• Save the MacroController.xlsm file.
• Press Alt + F8, select UpdateMasterFile, and click Run.

Notes

• Ensure all source files are closed before running the macro.
• The macro will open the master workbook, delete the specified tabs, copy the new tabs from the source files, and then save and close the master workbook.
• The message box will confirm the successful update of tabs once the process is complete.

By using this separate controller workbook, you can run the macro without manually opening the master file. This approach should streamline the process and prevent any conflicts from having the master file open during the macro execution. The text, steps and code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.