Skip to content
The Chat Archive
gpt-3.5-turbo-0301· OpenAI WildChat-4.8M (AI2)

I have two workbooks open. One is called 'Service Providers' the other is called…

  1. 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

  2. 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 Sub
    

    Note: 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.

  3. User

    Auxiliary will be the active workbook

  4. 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 Sub
    

    Note: This code should be placed in the 'Auxiliary' workbook module, as described in my previous answer.