Merge Data from Multiple Workbooks into One

CDThe1

New Member
Joined
Mar 27, 2016
Messages
10
Hello, I have seen variations of this question, but nothing that fits for me. Here's what I want.

I have a "master" file and about 50 source files I want to extract data from. (I just want to copy one column or range from the source files). I need a VBA that will open the source file "ABC" with one sheet named "1000" and paste in into the "master" file in a sheet with the same name. The next source file is "XYZ" with one sheet named "1200" and copy it in the master file on sheet "1200."

The trouble is, the source filenames will change from one month to the next., so I would need to reference a specific cell for the correct filename to use.

Hope that makes sense.

Thanks in advance.
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
Hi, you replied to my need for help quickly. Thank you. The below macro works perfectly. Thank you! My range column changed from C to Z. When I tried to update the macro, it did not copy each worksheet. Also, now that I recopied your macro into VBA and change the range from C to Z, it causes Excel to crash. Any ideas?
You quoted both of us. Were you talking to me or him?

If me, as you say, just change "C10:C256" to "Z10:Z256" in this test sub
VBA Code:
Sub Test__Get_All_Source_File_Names_With_This_File_Extension_From_This_FolderPath_Along_With_All_Of_Their_Sheet_Names_And_Put_Them_Into_A_Directory_Table_In_This_Sheet()
Call Get_All_Source_File_Names_With_This_File_Extension_From_This_FolderPath_Along_With_All_Of_Their_Sheet_Names_And_Put_Them_Into_A_Directory_Table_In_This_Sheet( _
ThisWorkbook.Sheets("Data Retrieval Sheet").Range("A2").Value & "\", ".xlsx", _
"Data Retrieval Sheet", _
"C10:C256" _
)
End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi, this works perfectly, but does not import every worksheet.
What is problem with sheets not copied?
Are you sure you don't have more space at the first or end of worksheet name at Source or Master Workbook.
And for change range until column Z at my code change related line to:
VBA Code:
 xTWB.Sheets(Z - 1).Range("C10:Z256").Value = xWS.Range("C10:Z256").Value
 

CDThe1

New Member
Joined
Mar 27, 2016
Messages
10
What is problem with sheets not copied?
Are you sure you don't have more space at the first or end of worksheet name at Source or Master Workbook.
And for change range until column Z at my code change related line to:
VBA Code:
 xTWB.Sheets(Z - 1).Range("C10:Z256").Value = xWS.Range("C10:Z256").Value
Does this need to be changed to "Z" also?

Lr = xWS.Range("C" & Rows.Count).End(x1up).Row
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
No. Because you want only need to paste data at fixed range, You can delete this line also
 

CDThe1

New Member
Joined
Mar 27, 2016
Messages
10
You quoted both of us. Were you talking to me or him?

If me, as you say, just change "C10:C256" to "Z10:Z256" in this test sub
VBA Code:
Sub Test__Get_All_Source_File_Names_With_This_File_Extension_From_This_FolderPath_Along_With_All_Of_Their_Sheet_Names_And_Put_Them_Into_A_Directory_Table_In_This_Sheet()
Call Get_All_Source_File_Names_With_This_File_Extension_From_This_FolderPath_Along_With_All_Of_Their_Sheet_Names_And_Put_Them_Into_A_Directory_Table_In_This_Sheet( _
ThisWorkbook.Sheets("Data Retrieval Sheet").Range("A2").Value & "\", ".xlsx", _
"Data Retrieval Sheet", _
"C10:C256" _
)
End Sub
Sorry for the confusion. Your macro is perfect. I just had to update my ranges. Thanks so much.
 

Forum statistics

Threads
1,148,364
Messages
5,746,276
Members
424,003
Latest member
paaskanama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top