I'm seeking assistance in creating a VBA macro that can help me merge data from multiple workbooks located in different folders based on order numbers in another excel file. I am beginner and not having enough knowledge in VBA. It would be a kind help to achieve my migration activity.
Here's the scenario:
I have four folders: Folder 1, Folder 2, Folder 3, and Folder 4. Folder 1 contains a single Excel workbook with one sheet. Folder 2 contains around 300 data workbooks, each named after an order number. Folder 3 contains a single Excel workbook with one sheet. Folder 4 contains the same number of files as Folder 2, with filenames corresponding to the order numbers. I need to read the workbooks in all the folders and create a new workbook that combines the four excel workbook sheets to one workbook. The final workbook should be renamed based on a Folder 2 file name or I can keep open a separate excel file with list of order numbers to consider for renaming.
Requirements:
Preserve the existing sheet names in the merged workbook. All four worksheets in final workbook should be by sequence based on folder reading.
I would greatly appreciate it if someone could help me with the VBA code to achieve this. Any guidance, suggestions, or sample code would be extremely helpful.
Thank you in advance for your assistance!
Here is the code i am trying but not getting through.
Sub MergeWorkbooks()
Dim FolderPath1 As String, FolderPath2 As String, FolderPath3 As String, FolderPath4 As String
Dim OrderNumbersFilePath As String
Dim OrderNumbersWorkbook As Workbook
Dim OrderNumbersSheet As Worksheet
Dim FinalWorkbook As Workbook
Dim DataWorkbook As Workbook
Dim OrderNumber As String
' Set the folder paths for each folder
FolderPath1 = "Path\to\Folder1\"
FolderPath2 = "Path\to\Folder2\"
FolderPath3 = "Path\to\Folder3\"
FolderPath4 = "Path\to\Folder4\"
' Set the file path of the workbook containing order numbers
OrderNumbersFilePath = "C:\OrderNumbersWorkbook.xlsx"
' Open the workbook containing order numbers
Set OrderNumbersWorkbook = Workbooks.Open(OrderNumbersFilePath)
Set OrderNumbersSheet = OrderNumbersWorkbook.Worksheets(1)
' Create a new workbook for the final result
Set FinalWorkbook = Workbooks.Add
' Loop through each order number in the list
For i = 1 To OrderNumbersSheet.Cells(Rows.Count, 1).End(xlUp).Row
OrderNumber = OrderNumbersSheet.Cells(i, 1).Value
' Open the corresponding data workbook from Folder2
Set DataWorkbook = Workbooks.Open(FolderPath2 & "\" & OrderNumber & ".xlsx")
' Copy the data from the data workbook to the final workbook
DataWorkbook.Worksheets(1).Copy After:=FinalWorkbook.Sheets(FinalWorkbook.Sheets.Count)
FinalWorkbook.Sheets(FinalWorkbook.Sheets.Count).Name = OrderNumber
' Close the data workbook without saving changes
DataWorkbook.Close SaveChanges:=False
' Open the corresponding workbook from Folder4
Set DataWorkbook = Workbooks.Open(FolderPath4 & "\" & OrderNumber & ".xlsx")
' Copy the data from the workbook to the final workbook
DataWorkbook.Worksheets(1).UsedRange.Copy Destination:=FinalWorkbook.Sheets(OrderNumber).Cells(Rows.Count, 1).End(xlUp).Offset(1)
' Close the workbook without saving changes
DataWorkbook.Close SaveChanges:=False
Next i
' Save and close the final workbook
FinalWorkbook.SaveAs "C:\to\Output_Workbook.xlsx"
FinalWorkbook.Close
' Close the order numbers workbook
OrderNumbersWorkbook.Close
End Sub
I tried to execute the above code but some how it is not accurately reading the files and preparing the workbook.
Here's the scenario:
I have four folders: Folder 1, Folder 2, Folder 3, and Folder 4. Folder 1 contains a single Excel workbook with one sheet. Folder 2 contains around 300 data workbooks, each named after an order number. Folder 3 contains a single Excel workbook with one sheet. Folder 4 contains the same number of files as Folder 2, with filenames corresponding to the order numbers. I need to read the workbooks in all the folders and create a new workbook that combines the four excel workbook sheets to one workbook. The final workbook should be renamed based on a Folder 2 file name or I can keep open a separate excel file with list of order numbers to consider for renaming.
Requirements:
Preserve the existing sheet names in the merged workbook. All four worksheets in final workbook should be by sequence based on folder reading.
I would greatly appreciate it if someone could help me with the VBA code to achieve this. Any guidance, suggestions, or sample code would be extremely helpful.
Thank you in advance for your assistance!
Here is the code i am trying but not getting through.
Sub MergeWorkbooks()
Dim FolderPath1 As String, FolderPath2 As String, FolderPath3 As String, FolderPath4 As String
Dim OrderNumbersFilePath As String
Dim OrderNumbersWorkbook As Workbook
Dim OrderNumbersSheet As Worksheet
Dim FinalWorkbook As Workbook
Dim DataWorkbook As Workbook
Dim OrderNumber As String
' Set the folder paths for each folder
FolderPath1 = "Path\to\Folder1\"
FolderPath2 = "Path\to\Folder2\"
FolderPath3 = "Path\to\Folder3\"
FolderPath4 = "Path\to\Folder4\"
' Set the file path of the workbook containing order numbers
OrderNumbersFilePath = "C:\OrderNumbersWorkbook.xlsx"
' Open the workbook containing order numbers
Set OrderNumbersWorkbook = Workbooks.Open(OrderNumbersFilePath)
Set OrderNumbersSheet = OrderNumbersWorkbook.Worksheets(1)
' Create a new workbook for the final result
Set FinalWorkbook = Workbooks.Add
' Loop through each order number in the list
For i = 1 To OrderNumbersSheet.Cells(Rows.Count, 1).End(xlUp).Row
OrderNumber = OrderNumbersSheet.Cells(i, 1).Value
' Open the corresponding data workbook from Folder2
Set DataWorkbook = Workbooks.Open(FolderPath2 & "\" & OrderNumber & ".xlsx")
' Copy the data from the data workbook to the final workbook
DataWorkbook.Worksheets(1).Copy After:=FinalWorkbook.Sheets(FinalWorkbook.Sheets.Count)
FinalWorkbook.Sheets(FinalWorkbook.Sheets.Count).Name = OrderNumber
' Close the data workbook without saving changes
DataWorkbook.Close SaveChanges:=False
' Open the corresponding workbook from Folder4
Set DataWorkbook = Workbooks.Open(FolderPath4 & "\" & OrderNumber & ".xlsx")
' Copy the data from the workbook to the final workbook
DataWorkbook.Worksheets(1).UsedRange.Copy Destination:=FinalWorkbook.Sheets(OrderNumber).Cells(Rows.Count, 1).End(xlUp).Offset(1)
' Close the workbook without saving changes
DataWorkbook.Close SaveChanges:=False
Next i
' Save and close the final workbook
FinalWorkbook.SaveAs "C:\to\Output_Workbook.xlsx"
FinalWorkbook.Close
' Close the order numbers workbook
OrderNumbersWorkbook.Close
End Sub
I tried to execute the above code but some how it is not accurately reading the files and preparing the workbook.
