I have a master workbook that loops through and opens workbooks one at a time.
The code opens a workbook, refreshes links, saves, close. However, the macro errors out only on some workbooks. It opens the workbook, but fails to save the workbook.
Code
Public myfilename As String
Public locn As String
Public contact As String
Public mypath As String
Public subject1 As String
Public signature As String
Sub mgmtrefresh()
Dim ws As Worksheet
Dim mgmtwb As Workbook
Dim wb1 As String
'Dim myfilename As String
'Dim locn As String
wb1 = ThisWorkbook.Name
Workbooks(wb1).Activate
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
'START LOOP
'Set Counter (Do Until # should be set as Counter for last item on Locs List + 1)
Sheets("Mapper").Activate
Set counter = Range("Counter")
Range("Counter").Value = Range("startcounter") 'cell f2
Do Until counter = Range("endcounter") 'cell g2
Worksheets("Mapper").Calculate
mypath = Range("mypath")
myfilename = Range("myfilename")
locn = Range("locn")
path = Range("path")
contact = Range("contact")
subject1 = Range("subject1")
signature = Range("signature")
'Open individul file template
Workbooks.Open mypath, UpdateLinks:=1
'Set mgmtwb = Workbooks.Open(mypath)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
' Select Individual template
Workbooks(myfilename).Activate
'Individual template
Application.DisplayAlerts = False
Application.Calculate
' Workbooks(myfilename).RefreshAll
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
' ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
' Worksheets("Mapper").Calculate
' Worksheets("High Level").Calculate
Application.Calculate
'Call mgmtemails
Workbooks(myfilename).Close SaveChanges:=True
' ActiveWorkbook.Save
' ActiveWorkbook.Close
Application.DisplayAlerts = True
'Activate main macro
Workbooks(wb1).Activate
Application.CutCopyMode = False
Sheets("Mapper").Activate
Range("Counter").Value = counter + 1
'END LOOP
Loop
'Load timestamps
'Call LastFileDateTime_MgmtReports
'Save mapper file
Workbooks(wb1).Saved = True
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub
Display More