Copy Data Between Excel Workbooks by Matching Worksheet Names with VBA in WPS Office
Question details
The user needs VBA code that copies a range from source worksheets to destination worksheets with the same names, even when the sheet order differs, and places the copy after the first table in each target sheet.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has two Excel workbooks. The source and destination worksheets have matching names but appear in different orders, and each destination copy must start after the first table on the matching destination sheet.
- Observed behavior
- The macro should match worksheets by name, copy the specified range, and paste only values and number formats at the calculated destination row.
Open both workbooks in WPS Office Spreadsheets. If the code will be stored in the workbook, save macro-enabled copies as .xlsm and confirm that the source sheet names exactly match the destination sheet names.
Run a name-matching VBA macro in WPS Office Spreadsheets
Use this solution when the source and destination worksheets are in different orders and the paste row must be calculated from the last used row in each destination sheet.
Paste this VBA code into a standard module. It loops through all sheets in the source workbook, finds a sheet with the same name in the destination workbook, calculates the destination row after the last used cell in column A, and pastes values and number formats.
Sub CopyBySheetName() Dim wb1 As Workbook, wb2 As Workbook Dim ws1 As Worksheet, ws2 As Worksheet Dim lastRow As Long, destinationRow As Long Set wb1 = Workbooks("Destination.xlsx") Set wb2 = Workbooks("Source.xlsx") For Each ws2 In wb2.Worksheets On Error Resume Next Set ws1 = wb1.Worksheets(ws2.Name) On Error GoTo 0 If Not ws1 Is Nothing Then lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row destinationRow = lastRow + 3 ws2.Range("A4:M9").Copy ws1.Cells(destinationRow, "A").PasteSpecial Paste:=xlPasteValuesAndNumberFormats End If Set ws1 = Nothing Next ws2 End Sub
Open both workbooks in WPS Office Spreadsheets, then press Alt+F11 to open the VBA editor.
In the VBA editor, click Insert > Module to create a new standard module.
Paste the VBA code above into the module. Change "Destination.xlsx" and "Source.xlsx" to your actual workbook names, and change "A4:M9" to the range you need to copy.
Click inside the CopyBySheetName macro and press F5, or click Run > Run Sub/UserForm. The matching sheets are updated by name.
Copy Excel Workbook Data by Matching Sheet Names with a VBA Macro in WPS Office
WPS Office Spreadsheets can run the same VBA macro to copy ranges between workbooks by worksheet name, calculate the paste row, and paste values and number formats without linking formulas.
- 1. Download WPS Office: Go to the official WPS Office download page and install WPS Office on your computer.
- 2. Open the two Excel workbooks: Launch WPS Office Spreadsheets and open both the source and destination .xlsx or .xlsm workbooks.
- 3. Open the VBA editor: Press Alt+F11 in WPS Office Spreadsheets to open the VBA editor.
- 4. Add the macro: Insert a module and paste the name-matching VBA macro from the solution above.
- 5. Run the copy: Press F5 or click Run > Run Sub/UserForm to copy data between the matching worksheet names.
Frequently Asked Questions
How do I change the range that is copied between workbooks?
Edit the line ws2.Range("A4:M9").Copy in the VBA module and replace A4:M9 with the source range you need. Use a fully qualified range such as A4:M20 if the row count varies.
Why does the macro skip a worksheet when the name has extra spaces?
The match uses the exact worksheet name. If the source and destination names are not identical, the Set statement fails and the sheet is skipped. Rename the worksheets so they match exactly, including spaces and capitalization.
Can this macro copy formulas instead of only values?
Not as written. PasteSpecial Paste:=xlPasteValuesAndNumberFormats pastes values and number formats only. To copy formulas, use Paste:=xlPasteFormulas or xlPasteAll in a separate PasteSpecial call.
Where do I enable macros in WPS Office Spreadsheets?
Open the Options dialog from the Menu or File button in the top-left corner, go to Security or Macro Settings, and allow macros for the workbook. Save the file as .xlsm if the macro must be stored with the workbook.
