I have the following code which works fine if I open the template and manually save it. If I run it from an Access VBA module using the following SaveAS


Sub SaveAndClose(File As String)
Dim FileFormatValue As Long
Dim Sourcewb As Workbook
Dim fname As Variant
Dim SourceFileType As Long


Set Sourcewb = ActiveWorkbook
'ActiveWorkbook.SaveAs FileName:=File, FileFormat:=52, ConflictResolution:=xlUserResolution


SourceFileType = Sourcewb.FileFormat


ActiveWorkbook.SaveAs FileName:=File, FileFormat:=SourceFileType, ConflictResolution:=xlUserResolution


ActiveWorkbook.Close SaveChanges:=True


End Sub

----------------------------------------------------------------------------------------


It runs through the following code but does nothing. It doesn't even change worksheets.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


DisplayType_select
DisplayLocation_select
DisplaySize_select




End Sub


Sub DisplayType_select()
'
Application.ScreenUpdating = True
Workbooks("Weekly_OFD_Report_Template.xlsm").Activate
Workbooks("Weekly_OFD_Report_Template.xlsm").Worksheets("By_Fascia").Activate
'Sheets("By_Fascia").Select
On Error Resume Next
With Workbooks("Weekly_OFD_Report_Template.xlsm").Worksheets("By_Fascia").PivotTables("PivotTable5").PivotFields("Question")
.PivotItems("Display Type 1").Visible = True
.PivotItems("Display Type 2").Visible = True
.PivotItems("Display Type 3").Visible = True
.PivotItems("Display Type 4").Visible = True
.PivotItems("Display Type 5").Visible = True
.PivotItems("Display Type 6").Visible = True
.PivotItems("Display Type 7").Visible = True
.PivotItems("Display Type 8").Visible = True
.PivotItems("Display Type 9").Visible = True
.PivotItems("Display Type 10").Visible = True


End With
On Error GoTo 0
ActiveSheet.PivotTables("PivotTable5").RefreshTable
'ActiveSheet.PivotTables("PivotTable5").
On Error GoTo 0
End Sub


Any help would be much appreciated.


Thanks & Regards


David Scott