Excel: summing a specific column in multiple sheets and place result in new sheet

0
votes

Goal: To sum a specific column (M) and populate result in new sheet within the same workbook

My attempt:

Sub ramsum()
'
' ramsum Macro
'
    ActiveCell.FormulaR1C1 = "=SUM('02-06'!C[12])"
    Range("E20:E21").Select

End Sub

Issue: How to check the sheet name above and make it dynamic to ensure all contents of column M in the respective sheets are summed and results entered into new sheet.

Example

Worksheet - contains 30 sheets labelled 02-06, 03-06, 04-06 these represent days in June. Each sheet has in the order of 100 rows by columns. Column M has the data required to be summed. It varies in vertical lenght. Essentially, column contains numbers only and is to be summed and results placed in a new sheet. In summary all summed results of Column from the different sheets are to be placed into the same "summary sheet".

Please advise on

1) Modifying about code to point to all the other sheets (different names) 2) Or advise is easier method using sumif, indirect...or similar.

1
excelvba

1 Answers

0
votes

I am wondering somehow, why you cannot just sum up the whole column but need to specify the exact range, as of your description, there is nothing else in column M. If this is the case, then the following code, might help you:

Sub runningThroughSheets()

  Dim actBook As Workbook
  Dim wsht As Worksheet

  Set actBook = Application.ActiveWorkbook

  For Each wsht In actBook.Worksheets
    MsgBox wsht.Evaluate("SUM(M:M)")
  Next

End Sub