Jun 04 2024 10:46 AM - edited Jun 04 2024 10:49 AM
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!!
Jun 06 2024 08:45 AM
To accomplish this task, you will need a VBA macro that can handle the following tasks:
Here is a detailed VBA script that should help you achieve this:
Step-by-Step Guide
1. Open Excel:
2. Access the VBA Editor:
3. Copy and Paste the VBA Code:
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:
2. Define the Source Path:
3. List of Tab Names:
4. Delete Existing Tabs:
5. Loop Through Source Files:
6. Copy Tabs:
7. Close Source Workbook:
8. Completion Message:
Running the Macro
Notes:
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.
Jun 18 2024 11:10 AM
@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!
Jun 18 2024 06:34 PM
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
2. Access the VBA Editor
3. Copy and Paste the VBA Code
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
5. Running the Macro
Notes
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.