How to Copy Data Between Workbooks by Matching Sheet Names Using WPS Office VBA
Question details
The user needs VBA code that loops through worksheets in a source workbook, finds matching or partially matching sheet names in a destination or template workbook, and copies the source data to the correct sheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with VBA macros to transfer data between workbooks and wants to match source and destination worksheets by name, including partial matches for prefixed or suffixed sheet names.
- Observed behavior
- The macro should copy data from a source workbook into the matching sheet of a template or destination workbook without manual copying.
Make sure both the source workbook and destination/template workbook are saved in a trusted location and that macros are enabled in WPS Office. Open both workbooks before running the VBA code so the macro can reference them by name.
Copy Data by Exact Sheet Name with a SheetExists Function
Use this method when source and destination sheet names are identical and you want to loop through all worksheets safely without hard-coding each name.
The VBA code loops through every sheet in the destination workbook, checks whether the same sheet name exists in the source workbook, and copies the used range to the matching destination sheet. The SheetExists function prevents errors when a sheet name is missing.
Open the destination/template workbook and the source workbook in WPS Spreadsheets, then press Alt+F11 to open the VBA editor.
In the VBA editor, click Insert > Module, then paste the following code into the new module: Sub TransferData() Dim wkbS As Workbook Dim strName As String Dim shD As Worksheet Set wkbS = Workbooks("Source.xlsx") For Each shD In ThisWorkbook.Worksheets strName = shD.Name If SheetExists(strName, wkbS) Then wkbS.Sheets(strName).Cells.Copy shD.Paste shD.Range("A1") End If Next End Sub Function SheetExists(strName As String, wkbS As Workbook) As Boolean Dim sh As Worksheet On Error Resume Next Set sh = wkbS.Worksheets(strName) SheetExists = (Err.Number = 0) On Error GoTo 0 End Function
Place the cursor inside TransferData and click Run > Run Sub/UserForm, or press F5. The macro copies data from each matching source sheet to the same-named destination sheet.
Copy Data by Partial Sheet Name with InStr Matching
Use this method when source sheet names contain prefixes or suffixes, such as "2024_Sales" and "Sales_Template", and you need to match a common text segment.
Copy Workbook Data by Matching Sheet Names with WPS Office VBA
Use the built-in VBA editor in WPS Spreadsheets to run a macro that loops through worksheets, matches sheet names across workbooks, and copies data automatically. WPS Office preserves standard Excel VBA syntax and supports .xlsx and .xlsm workbooks, so you can automate this task without switching tools.
- 1. Download and install WPS Office: Download WPS Office from the official WPS website, install it, and launch WPS Spreadsheets.
- 2. Open your source and destination workbooks: Open the Excel source workbook and the destination/template workbook in WPS Spreadsheets. Enable macros if prompted.
- 3. Open the VBA editor and insert a module: Press Alt+F11 to open the VBA editor, click Insert > Module, and paste the VBA macro for copying data by matching sheet names.
- 4. Run the macro: Select the macro and press F5, or click Run > Run Sub/UserForm, to copy the matching sheet data.
- 5. Save the destination workbook: Save the destination workbook in .xlsm format if it contains the macro, or .xlsx if you only need the copied data.
Frequently Asked Questions
Why does my VBA macro show 'Argument not optional' when using SheetExists?
This error usually means the function is called without its required arguments, or the function name was changed in the return line. In WPS VBA, make sure the function signature is Function SheetExists(strName As String, wkbS As Workbook) As Boolean and that the code assigns the result with SheetExists = ... using that exact function name. Call it as SheetExists(strName, wkbS).
Can I run Excel VBA that copies between workbooks in WPS Office?
Yes. WPS Spreadsheets supports common Excel VBA syntax and macros. Open both workbooks, press Alt+F11, insert a module, paste the code, and run it from the VBA editor or Macro dialog. Save macro-enabled files in .xlsm format to retain the VBA project.
How do I match partially matching sheet names instead of exact names?
Use InStr(1, shS.Name, strName, vbTextCompare) > 0 inside the source-sheet loop to find sheet names that contain the target text. Store the common text segment in a variable if the prefix or suffix varies, and add Exit For after copying the first match to avoid duplicate copies.
Why is data not copying when the sheet names do not match exactly?
The SheetExists method uses wkbS.Worksheets(strName), which only succeeds for an exact sheet-name match. If the actual sheet name has leading or trailing spaces, prefixes, suffixes, or spelling differences, the function returns False. Use exact naming or switch to the InStr-based partial-match macro.
How do I save a WPS Spreadsheets file with VBA macros?
In WPS Spreadsheets, choose File > Save As and select the Excel Macro-Enabled Workbook (*.xlsm) format. This preserves the VBA project so the macro can run again.
