In folder ‘SERVICE PROVIDERS’, I have a workbook named ‘Service Providers’ and within…
- 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.
- 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 SubTo use this code, follow these steps:
- Open both the 'Service Providers' and 'BudgetDetails' workbooks.
- Press
Alt+F11to open the VBA editor. - In the VBA editor, go to
Insert>Moduleto insert a new module. - Copy and paste the above code into the new module.
- Close the VBA editor.
- Press
Alt+F8to open the Macro dialog box. - 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.
- User
Where in the workbook BudgetDetails do I call this code
- 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:
- Open the 'BudgetDetails' workbook.
- Go to the 'SectorBudget' sheet (the sheet where you want to copy the values and format).
- 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 Ribbonand check the box for 'Developer'. - Click on the 'Insert' button in the 'Controls' group, and select a button or a shape that you want to add to the sheet.
- Draw the button or shape on the sheet.
- In the 'Assign Macro' dialog box that appears, select the 'CopyValuesAndFormat' macro and click 'OK'.
- Close the 'Developer' tab.
- 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:
- Press
Alt+F11to open the VBA editor. - In the VBA editor, go to
View>Project Explorer. If you don't see the 'Project Explorer' window, you can also useCtrl+Rto toggle it on/off. - In the 'Project Explorer' window, expand the 'Microsoft Excel Objects' folder and double-click on the 'Sheet' that corresponds to the 'SectorBudget' sheet.
- In the worksheet's code window, paste the code snippet below:
Private Sub Worksheet_Activate() CopyValuesAndFormat End Sub- Close the VBA editor.
- 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.
- User
can the code be called automatically when the workbook opens
- 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:
- Open the 'BudgetDetails' workbook.
- Press
Alt+F11to open the VBA editor. - In the VBA editor, double-click on the 'ThisWorkbook' object in the 'Project Explorer' window.
- In the 'Workbook' code window, paste the code snippet below:
Private Sub Workbook_Open() CopyValuesAndFormat End Sub- Close the VBA editor.
- Save and close the 'BudgetDetails' workbook.
- 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.