Before running this code, the Workbook 'Service Providers' is already open. I am getting…
- User
Before running this code, the Workbook 'Service Providers' is already open. I am getting an error in his line 'With NoteWindow.Window(1)' in the following code: Public Sub OpenNotesWindow() Dim NoteWindow As Workbook Dim NoteSheet As Worksheet Dim sp As Workbook Dim nts As Worksheet
Set NoteWindow = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Auxillary.xlsm") Set NoteSheet = NoteWindow.Worksheets("Note") Set sp = Workbooks("Service Providers.xlsm") Set NotesSheet = sp.Worksheets("Notes")
With NoteWindow.Window(1) .DisplayWorkbookTabs = False .WindowState = xlNormal .Height = 500 .Width = 800 End With
sp.NotesSheet.Range("A1").Value = ThisWorkbook.Worksheets("Note").Range("A1").Value sp.NotesSheet.Range("A2").Value = ThisWorkbook.Worksheets("Note").Range("A2").Value 'copy and paste this line for the rest of the cells you want to display
NoteWindow.Window(1).Visible = True End Sub
- Assistant
The error in the line 'With NoteWindow.Window(1)' might be due to the fact that the NoteWindow workbook may not have a visible window or may have multiple windows. Instead, try using the following code to ensure you are working with the correct window:
Dim WN As Window Set WN = NoteWindow.Windows(1)
With WN .DisplayWorkbookTabs = False .WindowState = xlNormal .Height = 500 .Width = 800 End With
Alternatively, you can use the more concise method:
NoteWindow.Windows(1).Visible = True
This should allow you to successfully display the window you want.