Hello - I want users to:
1) Open a workbook file and it automatically runs the following:
2) Opens a New Window to the "Executive & Drill" Sheet. (The Workbook opens to "Dashboard" Sheet and contains a "Legend" Sheet as well)
3) Apply all sheets' gridline, freeze panes, formula bar, etc. settings throughout the workbook
4) Arranges the Two Excel Windows vertically (side by side)
The below already accomplishes most of #2 and #3 but Private Sub Workbook_Open() does not automatically run the maco upon opening. Also, when activated, the new window opens to "Dashboard" not "Executive Drill".
Any help is appreciated.
Private Sub Workbook_Open()
'Create a new window and apply the grid line settings
'for each sheet.
Dim ws As Worksheet
Dim i As Long
Dim bGrid As Boolean
Dim bPanes As Boolean
Dim iSplitRow As Long
Dim iSplitCol As Long
Dim iActive As Long
'Application.ScreenUpdating = False
'Store the active sheet
iActive = ActiveSheet.Index
'Create new window
ActiveWindow.NewWindow
'Loop through worksheets of original workbook
'and apply grid line settings to each sheet.
For i = 2 To ActiveWorkbook.Windows.Count
For Each ws In ActiveWorkbook.Worksheets
Windows(ActiveWorkbook.Name & " - 1").Activate
ws.Activate
bGrid = ActiveWindow.DisplayGridlines
'Get freeze panes
bPanes = ActiveWindow.FreezePanes
If bPanes Then
iSplitRow = ActiveWindow.SplitRow
iSplitCol = ActiveWindow.SplitColumn
End If
Windows(ActiveWorkbook.Name & " - " & i).Activate
Worksheets(ws.Index).Activate
ActiveWindow.DisplayGridlines = bGrid
If bPanes Then
ActiveWindow.SplitRow = iSplitRow
ActiveWindow.SplitColumn = iSplitCol
ActiveWindow.FreezePanes = True
End If
Next ws
'Activate original active sheet
Worksheets(iActive).Activate
Next i
'Activate the original active sheet
Windows(ActiveWorkbook.Name & " - 1").Activate
Worksheets(iActive).Activate
Application.ScreenUpdating = True
End Sub
Bookmarks