Copy Summary Values to Multiple Excel Worksheets with VBA
Question details
Copy each value from a summary sheet to the same cell on its corresponding detail worksheet when the number of detail sheets varies.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An exported workbook contains an index sheet, a summary sheet, and a variable number of test detail sheets.
- Observed behavior
- Each detail sheet should receive its corresponding summary value, such as C3 on the summary sheet going to F3 on the first detail sheet.
Check that the index is the first worksheet, the summary is the second, and detail worksheets follow in the same order as the values in summary column C. The macro includes hidden worksheets and overwrites existing values in F3, so check the sheet order and destination cells before running it.
Run a VBA macro to copy each summary value to its detail sheet
Use this approach to populate F3 on every detail worksheet, including when the number of detail sheets changes.
The macro maps summary cell C3 to F3 on the third worksheet, C4 to F3 on the fourth worksheet, and so on. It copies values rather than formulas and uses the worksheet order, not sheet names, to determine the mapping.
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).
Open the VBA editor with Alt+F11 or Developer > Visual Basic. Select the workbook in the Project pane, then choose Insert > Module.
Paste this code into the module: Sub CopySummaryValues() Dim wb As Workbook Dim i As Long Set wb = ThisWorkbook For i = 3 To wb.Worksheets.Count wb.Worksheets(i).Range("F3").Value2 = wb.Worksheets(2).Cells(i, "C").Value2 Next i End Sub
Return to Excel, open Developer > Macros, select CopySummaryValues, and choose Run. Check F3 on several detail sheets against the corresponding cells in summary column C.

Copy summary values to detail sheets in WPS Spreadsheets
For a smaller workbook, use Paste Special in WPS Spreadsheets to place each summary value in the corresponding detail sheet without a macro. Repeat the process when new detail sheets are added.
- 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select the workbook.
- 2. Copy the first value: On the summary sheet, select C3 and choose Copy.
- 3. Paste into the detail sheet: Open the first detail sheet, select F3, and use Paste Special > Values.
- 4. Repeat for the remaining sheets: Copy C4 from the summary sheet and paste its value into F3 on the next detail sheet. Continue with the following summary cells and corresponding sheets, then save the workbook.

Frequently Asked Questions
How does the macro match each value to a worksheet?
It uses worksheet position: summary C3 goes to F3 on worksheet 3, C4 goes to F3 on worksheet 4, and so on. Reordering or inserting worksheets can change the mapping.
Will the macro update new detail sheets automatically?
No. After adding detail sheets and their corresponding summary values, run CopySummaryValues again from Developer > Macros.
How can I copy values to A1 instead of F3?
In the VBA module, change Range("F3") to Range("A1"), then run the macro again.
Can I use a formula instead of copying a value?
Yes. On the first detail sheet, enter a formula such as ='Summary'!C3 in F3, replacing Summary with the actual sheet name. Use the corresponding summary row on each other detail sheet. Unlike the macro's copied values, formulas update when the source changes.




