VBA & Macro Problems

Copy Summary Values to Multiple Excel Worksheets with VBA

Bushra ParveenBushra Parveen Nov 22, 2026 868 views

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.

Copy Summary Values to Multiple Excel Worksheets with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Save as a macro-enabled workbook

In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

2
Add a VBA module

Open the VBA editor with Alt+F11 or Developer > Visual Basic. Select the workbook in the Project pane, then choose Insert > Module.

3
Insert the macro

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

4
Run the macro

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.

Run a VBA macro to copy each summary value to its detail sheet
Adjust the source or destination: Change "C" in Cells(i, "C") to use another summary column, or change "F3" to the target cell. Run the macro again after adding detail sheets; it does not update them automatically.
Copy values with WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select the workbook.
  2. 2. Copy the first value: On the summary sheet, select C3 and choose Copy.
  3. 3. Paste into the detail sheet: Open the first detail sheet, select F3, and use Paste Special > Values.
  4. 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.
Copy values without transferring source formatting or formulasOpen and edit Microsoft Excel XLSX and XLS filesWork with multiple worksheets in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.