I have the following code, but the problem is that the workbooks do not close when the code is executed. Ideally, I would like each workbook to close once the code has finished executing on that specific workbook.
Public Sub Testing()
Dim Varbooks
Dim varBook
Dim wb As Excel.Workbook
Application.DisplayAlerts = False
Varbooks = Array("Fire", "Ice", "Wind", "Mountain", "Sun")
For Each varBook In Varbooks
Set wb = Workbooks.Open(Location of workbook & varBook)
With wb
.SaveAs Filename:="Enter File Name here"
End With
Next
Dim fileName1
Dim fileName2
Dim strPath1 As String
Dim strpath2 As String
Dim whichPath As String
Dim CurrentPath As String
CurrentPath = ActiveWorkbook.Path
On Error GoTo ErrorCatch
fileName1 = "Enter file Name Here"
fileName2 = "Enter file Name Here"
varWorksheets = Array(fileName1, fileName2)
Dim strPathArr()
ReDim strPathArr(1 To 2)
MsgBox varBook
For Each varBook In Varbooks
strPathArr(1) = "1st location to check for the varbook" & varBook
strPathArr(2) = "2nd location to check for the varbook" & varBook
For Each varWorksheet In varWorksheets
Set wb = Nothing
whichPath = InWhichPathArr(strPathArr, varBook, varWorksheet)
If Len(Trim(whichPath)) > 0 Then
Set wb = Workbooks.Open(Filename:=whichPath & "\" & varBook & varWorksheet)
End If
If Not wb Is Nothing Then
Dim wks As Worksheet, qt As QueryTable
For Each wks In wb.Worksheets
For Each qt In wks.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
Next wks
Set qt = Nothing
Set wks = Nothing
Application.DisplayAlerts = False
wb.SaveAs Filename:="Location and filename to save as"
End If
Next varWorksheet
GoTo ExitMacro
ErrorCatch:
MsgBox Err.Description
ExitMacro:
On Error GoTo 0
Next
Set wb = Workbooks.Open(Name & Location of workbook wantingn to open)
For Each varBook In Varbooks
On Error Resume Next
Dim ws As Object
Set ws = Nothing: Set ws = wb.Sheets(varBook)
On Error GoTo 0
If Not ws Is Nothing Then
ActiveWorkbook.SaveAs Filename:="Location and filename to save as"
End If
Next varBook
End Sub
Can someone tell me what needs to be added/modified to make the workbooks close once the code has executed?
Bookmarks