Close open files following "With Workbook.Open" cycle

Hi,

I created a macro that goes trhough all files in a specified folders, "extracts" 3 sheets and then copies these 3 sheets into a new folder, with a slightly different name.

I am puzzled on how to close the "original" file that I open, the ones that I cycle through; the final result is that I have all the "new" files saved and closed and in the correct folder, but I still have all the "source" files open when the process is finished. Minor nuisance, but still I would like to solve it.

Here below the code:

FolderName = "C:\Users\Research05\Desktop\Test"
DestFoldName = "C:\Users\Research05\Desktop\Data\Export"
If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
FName = Dir(FolderName & "*.xlsm")

'loop through the files
Do While Len(FName)

With Workbooks.Open(FolderName & FName, UpdateLinks:=0)

Dim AWBn As String
Dim DestName As String
AWBn = ActiveWorkbook.Name
DestName = Left(AWBn, InStr(AWBn, "Master.xlsm"))

Application.ScreenUpdating = False

Worksheets(Array("Sheets1", "Sheets2", "Sheets3")).Copy

Set Wb = ActiveWorkbook

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("U64").Select
Sheets("Sheet1").Select

Wb.SaveAs DestFoldName & "\" & DestName & " Data"
Wb.Close

End With

' go to the next file in the folder
FName = Dir

Loop

End Sub

Hi Accicarelli,

I would suggest this:

Bind the sourcesheet just after the opening, and close it just after saving the target.

With Workbooks.Open(FolderName & FName, UpdateLinks:=0)
Set WbSource = ActiveWorkbook
...
[other code]
...
WbSource.Close
End With
...

thank you, it's exactly the solution I was looking for!