Merge Data from Multiple Workbooks into One

[CDThe1]

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]

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

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

 

[maabadi]

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:

 xTWB.Sheets(Z - 1).Range("C10:Z256").Value = xWS.Range("C10:Z256").Value

 

[CDThe1]

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:

 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]

No. Because you want only need to paste data at fixed range, You can delete this line also

 

[CDThe1]

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

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.