Davie_Scott
New member
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
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