Forum Discussion

Lexrt999's avatar
Lexrt999
Copper Contributor
Jun 04, 2024

Macro to Bring Tab from Another File

Hello all,

I’m fairly new to macros and I have a complicated one that I’m trying to build.

 

Every month I bring into a master file 1 specific tab from 19 different files. The source files are all saved in the same file path but their names change each month. They each have one tab with a unique name i.e. one file has a tab called 111, the other file has a tab called 112, and so on. 

I have a master file for which I need to bring in those specific tabs. I do this by deleting the tabs from the master file that I brought in last month(there are other tabs on the master file) and opening each new source file and right click copy on the tab and copy it into the master file. I have to bring the entire tab because it’s got lots of formatting and a specific layout so I don’t want to use codes that simply copy and paste the data. I’m hoping to bring the entire tab. 

 

I’m trying to build a macro that I can run on an open workbook without stating the specific name or file path because my master file is in a different location and has a different name each month. So I just want to run the macro on the open file to delete tabs 111, 112 and so on and then have it go into this specific folder which doesn’t change and look in those files (which names vary ea. month) for tab 111, 112, 113 and so on in that specific order and bring them into the master file. 

 

Any guidance is appreciated!!

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Lexrt999 

    To accomplish this task, you will need a VBA macro that can handle the following tasks:

    1. Delete the old tabs from the master file.
    2. Open each source file in a specific directory.
    3. Copy the required tab from each source file to the master file.

    Here is a detailed VBA script that should help you achieve this:

    Step-by-Step Guide

    1. Open Excel:

    • Open your master workbook in Excel.

    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 first and then run the code.

    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
        
        ' Set your master workbook
        Set masterWorkbook = ThisWorkbook
        
        ' Define the path to the source files
        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")
        
        ' 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
        
        MsgBox "Tabs have been updated successfully.", vbInformation
    End Sub

    Explanation:

    1. Set the Master Workbook:

    • The masterWorkbook variable is set to the current workbook (the one running the macro).

    2. Define the Source Path:

    • Change sourcePath to the directory where your source files are stored.

    3. List of Tab Names:

    • tabNames is an array of tab names you want to copy from the source files.

    4. Delete Existing Tabs:

    • The macro loops through each tab name in tabNames and deletes them from the master workbook if they exist.

    5. Loop Through Source Files:

    • The macro uses the Dir function to loop through all Excel files in the source directory.
    • For each source file, it opens the workbook, copies the specified tabs, and then closes the workbook without saving.

    6. Copy Tabs:

    • For each tab name in tabNames, the macro tries to copy the tab from the source workbook to the master workbook.

    7. Close Source Workbook:

    • The source workbook is closed without saving after copying the required tabs.

    8. Completion Message:

    • A message box is displayed once the process is complete.

    Running the Macro

    • To run the macro, go back to Excel, press Alt + F8, select UpdateMasterFile, and click Run.

    Notes:

    • Ensure the source files and the master file are closed when running the macro to avoid conflicts.
    • The source files should have a consistent naming convention or be in the same directory as specified.
    • Adjust the sourcePath to the correct path of your source files.

    This script automates the entire process, so you do not need to manually open each source file. The text, steps and code were 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.

    • Lexrt999's avatar
      Lexrt999
      Copper Contributor

      NikolinoDE thank you so much! Part of the macro worked. It did delete the tabs I specified but it didn’t copy the new ones over. I did update the source path but I think it could be that I had the master file open? I know you suggested I have all the files closed. This might be a really dumb question but how can I run the macro without opening the master file if I copied the code on that file? Sorry if that’s a really dumb question lol thank you! 

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        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.

Resources