Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

In folder ‘SERVICE PROVIDERS’, I have a workbook named ‘Service Providers’ and within…

  1. User

    In folder ‘SERVICE PROVIDERS’, I have a workbook named ‘Service Providers’ and within this workbook, I have a sheet named ‘Sector Budget’. In the folder ‘SERVICE PROVIDERS’ I have a folder named ‘z BudgetDetails’ which has a workbook named ‘BudgetDetails’. In the workbook ‘BudgetDetails’ I have a worksheet named ‘SectorBudget’.

    I want the worksheet ‘SectorBudget’ in workbook ‘BudgetDetails’ to have a value and format only copy of the worksheet ‘Sector Budget’ in workbook ‘Service Providers’.

    When both workbooks are open, I want the worksheet ‘Sector Budget’ in workbook ‘Service Providers’ to be copied values and format only (without formulas) to the worksheet ‘SectorBudget’ in workbook ‘BudgetDetails’.

    If the workbook ‘BudgetDetails’ is open and the workbook ‘Service Providers’ is not open, the attempt to copy should be aborted.

    Is there a way to do this.

  2. Assistant

    Yes, you can achieve this by using VBA code in Excel. You can use the following code to copy the values and format from the 'Sector Budget' sheet in 'Service Providers' workbook to the 'SectorBudget' sheet in 'BudgetDetails' workbook:

    Sub CopyValuesAndFormat()
        Dim srcWorkbook As Workbook
        Dim destWorkbook As Workbook
        Dim srcSheet As Worksheet
        Dim destSheet As Worksheet
        
        ' Check if 'Service Providers' workbook is open
        On Error Resume Next
        Set srcWorkbook = Workbooks("Service Providers.xlsx")
        On Error GoTo 0
        
        If srcWorkbook Is Nothing Then
            MsgBox "The 'Service Providers' workbook is not open. Aborting."
            Exit Sub
        End If
        
        ' Check if 'BudgetDetails' workbook is open
        On Error Resume Next
        Set destWorkbook = Workbooks("BudgetDetails.xlsx")
        On Error GoTo 0
        
        If destWorkbook Is Nothing Then
            MsgBox "The 'BudgetDetails' workbook is not open. Aborting."
            Exit Sub
        End If
        
        ' Set the source and destination sheets
        Set srcSheet = srcWorkbook.Sheets("Sector Budget")
        Set destSheet = destWorkbook.Sheets("SectorBudget")
        
        ' Copy values and format
        srcSheet.UsedRange.Copy
        destSheet.Cells.Clear
        destSheet.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        
        Application.CutCopyMode = False
        
        MsgBox "Values and format copied successfully."
    End Sub
    

    To use this code, follow these steps:

    1. Open both the 'Service Providers' and 'BudgetDetails' workbooks.
    2. Press Alt+F11 to open the VBA editor.
    3. In the VBA editor, go to Insert > Module to insert a new module.
    4. Copy and paste the above code into the new module.
    5. Close the VBA editor.
    6. Press Alt+F8 to open the Macro dialog box.
    7. Select the 'CopyValuesAndFormat' macro and click 'Run'.

    This will copy the values and format from the 'Sector Budget' sheet in the 'Service Providers' workbook to the 'SectorBudget' sheet in the 'BudgetDetails' workbook, as long as both workbooks are open. If the 'Service Providers' workbook is not open, the macro will display a message and abort the copy operation.

  3. User

    Where in the workbook BudgetDetails do I call this code

  4. Assistant

    In the 'BudgetDetails' workbook, you can call the VBA code by assigning it to a button or a shape on a worksheet, or by running it manually from the VBA editor.

    Here's an example of how to assign the code to a button on the 'SectorBudget' sheet:

    1. Open the 'BudgetDetails' workbook.
    2. Go to the 'SectorBudget' sheet (the sheet where you want to copy the values and format).
    3. Go to the 'Developer' tab on the ribbon. If you don't see the 'Developer' tab, you may need to enable it first. To enable the 'Developer' tab, go to File > Options > Customize Ribbon and check the box for 'Developer'.
    4. Click on the 'Insert' button in the 'Controls' group, and select a button or a shape that you want to add to the sheet.
    5. Draw the button or shape on the sheet.
    6. In the 'Assign Macro' dialog box that appears, select the 'CopyValuesAndFormat' macro and click 'OK'.
    7. Close the 'Developer' tab.
    8. Now, whenever you click on the button or shape on the 'SectorBudget' sheet, it will execute the 'CopyValuesAndFormat' macro and copy the values and format from the 'Sector Budget' sheet in the 'Service Providers' workbook.

    Alternatively, you can run the macro manually from the VBA editor:

    1. Press Alt+F11 to open the VBA editor.
    2. In the VBA editor, go to View > Project Explorer. If you don't see the 'Project Explorer' window, you can also use Ctrl+R to toggle it on/off.
    3. In the 'Project Explorer' window, expand the 'Microsoft Excel Objects' folder and double-click on the 'Sheet' that corresponds to the 'SectorBudget' sheet.
    4. In the worksheet's code window, paste the code snippet below:
    Private Sub Worksheet_Activate()
        CopyValuesAndFormat
    End Sub
    
    1. Close the VBA editor.
    2. Now, whenever you activate the 'SectorBudget' sheet (e.g., by clicking on it or navigating to it), the 'CopyValuesAndFormat' macro will be automatically executed and copy the values and format from the 'Sector Budget' sheet in the 'Service Providers' workbook.

    Choose the option that suits your requirements and follow the corresponding steps to implement the code in the 'BudgetDetails' workbook.

  5. User

    can the code be called automatically when the workbook opens

  6. Assistant

    Yes, you can modify the code to automatically call the 'CopyValuesAndFormat' macro when the 'BudgetDetails' workbook opens. Here's how you can do it:

    1. Open the 'BudgetDetails' workbook.
    2. Press Alt+F11 to open the VBA editor.
    3. In the VBA editor, double-click on the 'ThisWorkbook' object in the 'Project Explorer' window.
    4. In the 'Workbook' code window, paste the code snippet below:
    Private Sub Workbook_Open()
        CopyValuesAndFormat
    End Sub
    
    1. Close the VBA editor.
    2. Save and close the 'BudgetDetails' workbook.
    3. Whenever you open the 'BudgetDetails' workbook now, the 'CopyValuesAndFormat' macro will automatically be called and copy the values and format from the 'Sector Budget' sheet in the 'Service Providers' workbook to the 'SectorBudget' sheet.

    This modification will ensure that the 'CopyValuesAndFormat' macro is automatically executed every time the 'BudgetDetails' workbook is opened, without the need for any manual intervention.