Excel: Totals from several worksheets into one worksheet 1

[Zariic]

Hello:
I need a lot of help on this one thing. I have one worksheet that is a list of other worksheets within the same workbook. In that list I need to have a total from each worksheet that corresponds with the worksheet name in the list. =sheetname!cell works except that I need "sheetname" to somehow be dynamic because new worksheets are constantly added and when a new worksheet is added, it adds that new worksheet name to the list. Is there a VBA formula that can do this or anyway that this can be done?

Please let me know if this is confusing. It probably is because it's confusing for me to try and describe what I'm trying to do. I'm not so sure I can describe it any other way though. :-/ Haha.

Thank you in advance,

-Joe

 

[Zathras]

Here is one way. I had to make a few assumptions, so if you set up a new workbook according to the following you should be able to see how to adapt this to your needs:

Assumptions:
1. Each worksheet except one is to be included in the recap.
2. Each worksheet to be recapped has the total in the same column, and it is the last thing in that column.

Setup:
1. Create a workbook with multiple sheets.
2. Label one of the sheets "Recap"
3. Label the other sheets with meaningful names. (E.g., January, February, etc.)
4. Select the "Recap" sheet and create a range with the name "RECAP" with the definition of

     $A$5:$B$259

5. Enter the following on the "Recap" sheet"

      A4: 'Grand Total
      B4: =SUM(B5:B259)

6. On each of the other sheets, place a number in column "E" on a randomly selected row to simulate the sheet totals.
7. Put the following macro in the sheet code for the "Recap" sheet"

Private Sub Worksheet_Activate()
  GenerateRecap
End Sub

8. Put the following macro into a module:

Option Explicit
Const RECAP_RANGE = "Recap"
Const COL_SHEETNAME = 1
Const COL_TOTAL = 2
Const SHEET_TOTAL_COLUMN = 5

'Column "E"

Sub GenerateRecap()
Dim sh As Worksheet
Dim rngRecap As Range
Dim rngTotal As Range
Dim nRecapRow As Integer

  Application.ScreenUpdating = False
  Set rngRecap = Range(RECAP_RANGE)
  rngRecap.ClearContents
  nRecapRow = 0
  For Each sh In Worksheets
    If sh.Name <> rngRecap.Worksheet.Name Then
      Set rngTotal = sh.Cells(65536, SHEET_TOTAL_COLUMN).End(xlUp)
      nRecapRow = nRecapRow + 1
      rngRecap.Cells(nRecapRow, COL_SHEETNAME) = sh.Name
      rngRecap.Cells(nRecapRow, COL_TOTAL) = rngTotal.Value
    End If
  Next sh
  Set rngTotal = Nothing
  Set rngRecap = Nothing
  Application.ScreenUpdating = True
End Sub

Test by changing values on the non-recap sheet(s) and selecting the recap sheet.

 

[Zariic]

Thanks so much, Zathras. I'm trying this stuff out today.

-Joe