I have two workbooks open. One is called 'Service Providers' the other is called…
- User
I have two workbooks open. One is called 'Service Providers' the other is called 'Auxillary'.
When I close 'Auxillary' I want all the values in A1:A31 its sheet 'Note' to be pasted into workbook 'Service Providers' sheet 'Notes' A1:A31.
How will I write this vba code
- Assistant
Assuming that the workbook 'Service Providers' is the active workbook, the following VBA code will copy the values from 'Auxiliary' sheet 'Note' range A1:A31 and paste into 'Service Providers' sheet 'Notes' range A1:A31 when 'Auxiliary' workbook is closed:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim auxWorkbook As Workbook Dim auxNote As Variant Application.ScreenUpdating = False Set auxWorkbook = Workbooks("Auxiliary") auxNote = auxWorkbook.Sheets("Note").Range("A1:A31").Value ThisWorkbook.Sheets("Notes").Range("A1:A31").Value = auxNote Application.ScreenUpdating = True End SubNote: This code should be placed in the 'Service Providers' workbook module. To access the module, right-click on the sheet tab and select 'View Code', then paste the code in the window that opens.
- User
Auxiliary will be the active workbook
- Assistant
In that case, you can use the following code in the 'Auxiliary' workbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim providerWorkbook As Workbook Dim providerNote As Variant Application.ScreenUpdating = False Set providerWorkbook = Workbooks("Service Providers") providerNote = Me.Sheets("Note").Range("A1:A31").Value providerWorkbook.Sheets("Notes").Range("A1:A31").Value = providerNote Application.ScreenUpdating = True End SubNote: This code should be placed in the 'Auxiliary' workbook module, as described in my previous answer.