Forum Discussion
Celia9
Sep 21, 2022Copper Contributor
Macro for Active workbook ADD -2
I have a macro to open another window beside my Excel workbook. For sharing purpose I will share the whole function.
The last part says to go to the second screen of the two windows ("Planning.xlsm - 2") and go to sheet R5 activity. But now, when I change the name of the file, I get on error.
How do I write "this workbook" - 2 in the macro?
Sub New_Window_Preserve_Settings()
'Create a new window and apply the grid line settings
'for each sheet.
Dim ws As Worksheet
Dim i As Long
Dim iWinCnt As Long
Dim bGrid As Boolean
Dim bPanes As Boolean
Dim bHeadings As Boolean
Dim iSplitRow As Long
Dim iSplitCol As Long
Dim iActive As Long
Dim iZoom As Long
Dim sSep As String
Application.ScreenUpdating = False
'Store the active sheet
iActive = ActiveSheet.Index
'Create new window
ActiveWindow.NewWindow
iWinCnt = ActiveWorkbook.Windows.Count
'Set the separator based on the version of Excel
'Office 365 now using a dash
If InStr(":", ActiveWorkbook.Name) > 0 Then
sSep = ":"
Else
sSep = " - "
End If
'Loop through worksheets of original workbook
'and apply grid line settings to each sheet.
For Each ws In ActiveWorkbook.Worksheets
Windows(ActiveWorkbook.Name & sSep & "1").Activate
ws.Activate
'Store the properties
bGrid = ActiveWindow.DisplayGridlines
bHeadings = ActiveWindow.DisplayHeadings
iZoom = ActiveWindow.Zoom
'Get freeze panes
bPanes = ActiveWindow.FreezePanes
If bPanes Then
iSplitRow = ActiveWindow.SplitRow
iSplitCol = ActiveWindow.SplitColumn
End If
'Activate the new window and sheet in loop
Windows(ActiveWorkbook.Name & sSep & iWinCnt).Activate
Worksheets(ws.Index).Activate
'Set properties
With ActiveWindow
.DisplayGridlines = bGrid
.DisplayHeadings = bHeadings
.Zoom = iZoom
If bPanes Then
.SplitRow = iSplitRow
.SplitColumn = iSplitCol
.FreezePanes = True
End If
End With
Next ws
'Activate original active sheet for the new window
Worksheets(iActive).Activate
'Activate the original active sheet for the original window
Windows(ActiveWorkbook.Name & sSep & "1").Activate
Worksheets(iActive).Activate
'Split Screen View (optional)
'The following section can be commented out if you don't want split screen.
'Turn screen updating on for split screen
Application.ScreenUpdating = True
For i = iWinCnt To 1 Step -1
Windows(ActiveWorkbook.Name & sSep & i).Activate
Next i
'Split view side-by-side vertical
ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlVertical
'Scroll to active tab in original window
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Sheets:=iActive
'Scroll to active tab in new window
Windows(ActiveWorkbook.Name & sSep & iWinCnt).Activate
DoEvents
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Sheets:=iActive
ActiveWorkbook.Name
Windows("Planning.xlsm - 2").Activate
Sheets("R5Activity").Select
End Sub
- Got it myself!
It was
ActiveWorkbook.Name & sSep & "2"
1 Reply
Sort By
- Celia9Copper ContributorGot it myself!
It was
ActiveWorkbook.Name & sSep & "2"