Article updated on February 07, 2009

Question: I have a workbook with many sheets. How do i sum values from same place on every sheet throughout the entire workbook?

Answer: You need to create a macro that creates an array of your sheet names.

I found a macro on C Pearson site.

How to use this macro on your workbook:

  1. Enable the developer tab on the ribbon.
  2. Go to developer tab.
  3. Click "Visual Basic"
  4. Right click with mouse button on VBAProject (Your file)
  5. Select Insert and Module
  6. Copy VBA from C Pearson site

How to retrieve and sum cell values on cell A1:A3 from every sheet:

  1. Use the VBA by typing =AllSheetNames() in a cell range (A1:A3) and press Ctrl + Shift + Enter
  2. In B1:B3 type "=INDIRECT(A1&"!"&"C1")

This will get all the values in all sheets in cell C1. See picture below.

How to refresh Sheet list if a sheet name is added or renamed:

  1. Select all cells that contain sheet names
  2. Press Ctrl +Shift + Enter