Need to create a Macro that goes to a workbook and a specific worksheet and copies a defined range of cells. It then returns to a centalized page where the data is pasted. This process continues for multiple workbooks until the loop is complete (The Directory Names and File Names for the workbooks are defined in a worksheet).
My problem is that I cannot find a way to close the workbooks that the initial data was copied from once that process has taken place. I cannot call out the actual name because there will be multiple ones used. I do not know how to make it work using the variable name I assigned (MyFile) either?
Any suggestions are appreciated. The code I used is as follows:
Sub CreateGroupProcurementsFile()
'
Application.ScreenUpdating = False
'create variable for user path and file
Dim MyPath
Dim MyFile
'start at first file
MACRO.Select
Range("A7").Select
'create loop for each file
Do Until ActiveCell = ""
Let MyPath = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
Let MyFile = ActiveCell.Value
ChDir MyPath
Workbooks.Open Filename:=MyFile, UpdateLinks:=0
'Copy and Paste Range file
Sheets("Plan").Select
Range("A51:T1200").Copy
Windows("GroupProcurements.xls").Activate
GroupProcurement.Select
Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
'move down to next file
MACRO.Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
'end of loop above
GroupProcurement.Select
End Sub
My problem is that I cannot find a way to close the workbooks that the initial data was copied from once that process has taken place. I cannot call out the actual name because there will be multiple ones used. I do not know how to make it work using the variable name I assigned (MyFile) either?
Any suggestions are appreciated. The code I used is as follows:
Sub CreateGroupProcurementsFile()
'
Application.ScreenUpdating = False
'create variable for user path and file
Dim MyPath
Dim MyFile
'start at first file
MACRO.Select
Range("A7").Select
'create loop for each file
Do Until ActiveCell = ""
Let MyPath = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
Let MyFile = ActiveCell.Value
ChDir MyPath
Workbooks.Open Filename:=MyFile, UpdateLinks:=0
'Copy and Paste Range file
Sheets("Plan").Select
Range("A51:T1200").Copy
Windows("GroupProcurements.xls").Activate
GroupProcurement.Select
Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
'move down to next file
MACRO.Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
'end of loop above
GroupProcurement.Select
End Sub