Summary:

  • “Save as” of multiple excel workbooks
  • Invert if neg when opening a newly created workbook
  • Solution?: Workbook_Open sub.
  • Issue: The Workbook_Open sub doesn’t work in new files. (error in title)

The objective of the main macro is to “Save as” docs and send to a new path. Issue pertains to the Invert If Negative in my graphs when I open the files that were “Saved as”. Solution? Create a Workbook_Open (This Workbook). Works fine with the original file + the Module and This Workbook are transferred in the new files. But I get error message ( in Title).

OPEN WORKBOOK (in 'This Workbook')
Public Sub Workbook_Open()
Run "Invertrednegative"
‘ (Error in title). References a sub at the end of the macro in module. The macro runs in the main doc but bugs in the new file
End Sub
MAIN MACRO (in a module):
Sub saveAll()
Dim unpass As String
Dim Pwd As String
Dim ws As Worksheet
Dim FormulaR1C1
Dim Cht As ChartObject
Dim sr As Series
'Dir 09
Sheets("Fund 41").Select
Sheets("Fund 41").Range("AD7").Select
Range("AD7").FormulaR1C1 = "'09"
'Refresh pivot tables update graphs
Call Refresh
Call Invertrednegative
Call Prot_All
'save doc
ChDir _
"C:\Users\XX\Desktop\Dashboards"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\XX\Desktop\Dashboards\EIO at " & Format(Now(), "yyyy_mm_dd"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Call Unprot_All
'Master cell
Sheets("Fund 41").Range("AD7").Select
Range("AD7").FormulaR1C1 = "'Master"
Call Refresh
'Protect all
Pwd = InputBox("Enter pass", "Pass Input")
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=Pwd, AllowFormattingCells:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
Next
'new master
ChDir _
"C:\Users\XXXXXX\Desktop\Dashboards"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\XXXXXX\Desktop\Dashboards\MASTER at " & Format(Now(), "yyyy_mm_dd"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
------------------------------------------------------------------------------------------------------------
Sub unprot_all_sheets()
Application.ScreenUpdating = False
On Error GoTo booboo
unpass = InputBox("Enter Pass")
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Application.ScreenUpdating = True
Exit Sub
booboo: MsgBox "There is a problem - check your password, capslock, etc."
End Sub
--------------------------------------------------------------------------------------------------------------------
Sub Prot_All()
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect Password:=unpass, AllowFormattingCells:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
Next
End Sub
---------------------------------------------------------
Sub Unprot_All()
Application.ScreenUpdating = False
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Application.ScreenUpdating = True
End Sub
----------------------------------------------------
Sub Refresh()
ThisWorkbook.RefreshAll
End Sub
------------------------------------------------------
Public Sub Invertrednegative()
For Each sht In ThisWorkbook.Worksheets
For Each Cht In sht.ChartObjects
For Each sr In Cht.Chart.SeriesCollection
sr.InvertIfNegative = True 'BUG
sr.InvertColor = vbRed
Next
Next
Next
End Sub