How to Copy Cell Values Between Workbooks When Sheet Names Match Using VBA
Question details
The user needs a VBA macro to loop through worksheets in a target workbook and copy specific cell values from a source workbook, provided the worksheet names match.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Transferring fixed cell values across multiple sheets in two different workbooks automatically by matching identical sheet names.
- Observed behavior
- A VBA macro is required to check for matching worksheet names across a source and target workbook and automatically copy values between predetermined cells without returning errors for missing sheets.
Ensure both the source and target workbooks are open in your spreadsheet application before running the macro, and make sure to back up your original data to avoid accidental overwrites during execution.
Use a VBA Macro to Copy Values Between Matching Worksheets
Execute a VBA script that loops through the target workbook, finds matching sheet names in the source workbook, and copies the defined cell values.
By utilizing the 'On Error Resume Next' statement, the macro safely checks if a sheet with an identical name exists in the source workbook. If a match is found, it copies the value from the defined source cell to the target cell while bypassing any sheets that do not have a match.
The following code is structured to disable Screen Updating while it runs, preventing your screen from flickering and significantly speeding up the transfer process.
Launch your spreadsheet application and open both the source workbook (e.g., 'logistic.xlsx') and the target workbook (e.g., 'production.xlsx').
Press 'Alt + F11' on your keyboard to open the Visual Basic Editor. Once opened, click 'Insert' > 'Module' from the top menu to create a new module.
Copy and paste the following macro code into the blank module window: Sub CopyCell() Const SourceBook = "logistic.xlsx" Const TargetBook = "production.xlsx" Const SourceCell = "P3" Const TargetCell = "L5" Dim wbkSource As Workbook Dim wbkTarget As Workbook Dim wshSource As Worksheet Dim wshTarget As Worksheet Application.ScreenUpdating = False Set wbkSource = Workbooks(SourceBook) Set wbkTarget = Workbooks(TargetBook) For Each wshTarget In wbkTarget.Worksheets Set wshSource = Nothing On Error Resume Next Set wshSource = wbkSource.Worksheets(wshTarget.Name) On Error GoTo 0 If Not wshSource Is Nothing Then wshTarget.Range(TargetCell).Value = wshSource.Range(SourceCell).Value End If Next wshTarget Application.ScreenUpdating = True End Sub
Update the constants at the top of the macro ('SourceBook', 'TargetBook', 'SourceCell', 'TargetCell') to match your actual workbook file names and the specific cell references you wish to copy.
Click the 'Run' button in the toolbar or press 'F5' to execute the script. The specified cell values will instantly copy across all matching worksheets.
Run Macros Seamlessly Using WPS Spreadsheet
WPS Office offers robust native support for VBA and Macros, allowing you to run complex Excel scripts directly without complicated configurations. You can easily automate cross-workbook data transfers natively in the integrated VBA environment.
- 1. Open Workbooks in WPS: Launch WPS Spreadsheet and open both the source and target files you need to work with.
- 2. Open the Visual Basic Editor: Navigate to the 'Tools' tab on the ribbon menu and select 'Macro' > 'Visual Basic Editor'.
- 3. Insert the Script: Click 'Insert' > 'Module' and paste your VBA code into the workspace, modifying the constants to fit your file details.
- 4. Execute Data Transfer: Press 'F5' to run the script. WPS Spreadsheet will swiftly execute the macro and populate your matching sheets.
Frequently Asked Questions
What happens if a worksheet name exists in the target workbook but not in the source workbook?
The provided macro includes error handling ('On Error Resume Next') designed for this exact scenario. It will safely skip any worksheet in the target workbook that does not have an identically-named corresponding sheet in the source workbook.
Can I copy a range of cells instead of a single cell using this macro?
Yes. You can modify the 'SourceCell' and 'TargetCell' constant values in the VBA script to refer to ranges (for example, "A1:B10") instead of single cells, provided the ranges are exactly the same size.
Why am I getting a 'Subscript out of range' error when running the macro?
This error typically occurs if the workbooks declared in the 'SourceBook' and 'TargetBook' constants are not currently open in the application, or if the file names specified in the code contain typos or missing file extensions (like missing the .xlsx suffix).
