Open and close source workbooks based on cell values in a Master Workbook

Question

Open and close source workbooks based on cell values in a Master Workbook

Anonymous

I am developing a macro that copies sheets from multiple different workbooks and pastes them into individual sheets into a Master workbook. I want the macro to close the source workbook(s) after copying the data from the desired sheet. Instead of replicating the code for each different source workbook that needs to get data pulled from (there are over 15 source workbooks to copy data from), I wanted some code to copy and close all the source workbooks based on cell values that are maintained on a sheet in the Master workbook. I was thinking I could incorporate a counter loop, and each time the data is copied and pasted into the Master workbook, that source workbook selected is then closed and then the next source workbook repeats the same process.

For example, cell C10 in the Master workbook equals the file name of the 1st source workbook and cell C11 in the Master workbook equals the file name of the 2nd source workbook, and so forth. The loop would continue to run up until there is no file name in the next C cell. Please see the code below for what I have thus far. The code works but it is inefficient and it's not currently dynamic, which is what I am hoping to accomplish with this post. Thanks in advance for any help.

Sub Get_Source_Data()

Dim FolderPath As String, Filepath As String, Filename As String

Application.ScreenUpdating = False

'Folder directory path - cell F10 is equal to the directory path of the 1st source workbook,
' FolderPath = Dir(Sheets("Control").Range("F10").Value) '- this line doesn't seem to work unfortunately.

FolderPath = "C:\\" - insert file path of source workbook (looking for this to be dynamic as well).

Filepath = FolderPath & "*.xls*"
Filename = Dir(Filepath)

Do While Filename <> ""

Workbooks. Open (Folder Path & Filename)
Sheets("4.1 Operating").Select
Cells. Select
Application.CutCopyMode = False
Selection. Copy

Workbooks("2023-24 Master Budget.xlsm").Activate
Sheets(1).Select
Sheets(Range("D10").Value).Select
Cells. Select
ActiveSheet.Paste

Application.DisplayAlerts = False

'Close selected source workbook using exact file name
Workbooks("2023-24 Budget_Commercialization.xlsx").Close SaveChanges:=False '- this is the name of 1st source workbook

'ATTEMPT TO SELECT WORKBOOK NAME BASED ON CELL VALUE IN MASTER WORKBOOK
'Workbooks(Workbooks("2023-24 Master Budget.xlsm").Select.Sheets("Control").Select.Range("c10").value).Close SaveChanges:=False

Application.ScreenUpdating = True

Filename = Dir

Loop

Application.DisplayAlerts = True

End Sub

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Answer 1

Thank you for your response. I still seem to be having some difficulty running the code. Could you please have another look? Note that I added:

   Dim myRange as Range

   Set myRange("C10:C27")

I am getting Run-time error '438': Object doesn't support this property or method, when running:

  For each cl in origFile.Range(myRange)

Thank you,

Mike

Answer 2

Name your range containing the list of source files - say, "myRange"

Sub ExtractData()

dim cl as Range

' This is your original file into which you're importing the data

Set origFile = Application.actieworkbook

' This will loop through each of your source files in the list

For each cl in origFile.Range(myRange)

   cFile = cl.value ' cFile is the next source file to be opened

' I would advise putting some error handling here in case the file can't be opened

   worbooks.open(Path+Folder+cFile)

  DO YOUR STUFF HERE .........

  workbooks(cFile).close True

Next

End Sub

Share via

Open and close source workbooks based on cell values in a Master Workbook

2 answers