VBA Force Recalculation of Workbook 1

[scribbler99]

I'm quite new to this VBA stuff and am obviously having difficulty understanding the different worksheet functions and where to place my code. I have a Summary Worksheet which (each time the workbook opens)I list in column A every worksheet name along with the sum of each corresponding worksheets range E2:E1000 to get a value off each sheet. My purpose of listing each worksheet name dynamically is because the user may Add, ammend, delete or rename these. This all works fine every time I open the work book but what I cannot get my head around is how I get the workbook to recalculate if anything is changed on any of the worksheets without closing the book and reopening it.

I have searched the web and thought I could use the following in the Workbook_SheetChange event

Worksheets("Summary").Application.Calculate

just as a test but this failed. I thought You guys would obviously know why. I have had a similar problem on forcing a change event before and Skip pointed me in the right direction but I obviously am not getting the strategy of where to put my code.

The code I am using is as follows;

Private Sub Workbook_Open()

Dim ws
Dim Counter As Integer
Counter = 0

MsgBox "There are " & ThisWorkbook.Worksheets.Count & " Worksheets in  this Excel Workbook"
For Each ws In ThisWorkbook.Worksheets
    Counter = Counter + 1
    'MsgBox ws.Name
    If Counter > 1 Then ' This is just to ignore the 1st sheet as that is the summary one
        'This next line is just to place a total value spent on each individual sheet
        Worksheets(ws.Name).Cells(1, 6).Value = Application.WorksheetFunction.Sum(Worksheets(ws.Name).Range("E2:E1000"))
        'This next line is just to list all worksheet names onto Sumamry Sheet
        Worksheets("Summary").Cells(Counter, 1).Value = ws.Name
        'This next line is to place a total value spent on the summary sheet alongside each sheet name
        Worksheets("Summary").Cells(Counter, 2).Value = Application.WorksheetFunction.Sum(Worksheets(ws.Name).Range("E2:E1000"))
    End If
Next ws

End Sub

 

[SkipVought]

hi,

I suppose that you have Calculation set to MANUAL.

in the workbook_sheetchange event

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'to calculate ONLY the sheet
  sh.calculate

  'to calculate ALL open workbooks in this instance of Excel
  application.calculate
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You might want to do this...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'to calculate ONLY the sheet AND the summary sheet
  sh.calculate
  Worksheets("Summary").calculate
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scribbler99]

Hi Skip

When I copy your code and paste into my workbook then test it by say adding a value into one of the cells in column E nothing happens, no recalculation or error message.

I have checked the options and it is set to auto calc.

 

[SkipVought]

Oh. You don't have FORMULAS on your Summary Sheet!!!

In a module...

Sub SumSummary()

    Dim ws As Worksheet, wsSUM As Worksheet, lRow As Long, nSUM
    
    Set wsSUM = Worksheets("Summary")
    lRow = 2
    
    MsgBox "There are " & ThisWorkbook.Worksheets.Count & " Worksheets in  this Excel Workbook"
    For Each ws In ThisWorkbook.Worksheets
        With ws
            nSUM = Application.Sum(.Range("E2:E1000"))
            Select Case .Name
                Case "Summary"  'this might not ALWAYS be the first sheet
                Case Else
                    'This next line is just to place a total value spent on each individual sheet
                    .Cells(1, 6).Value = nSUM
                    'This next line is just to list all worksheet names onto Sumamry Sheet
                    wsSUM.Cells(Counter, 1).Value = .Name
                    'This next line is to place a total value spent on the summary sheet alongside each sheet name
                    wsSUM.Cells(Counter, 2).Value = nSUM
                    lRow = lRow + 1
            End Select
        End With
    Next ws

End Sub

Then call this procadure in the workbook open and the workbook sheet change events.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, your coding technique is good. However, some things to consider. I took the sheet counter out, as sheets(1) may not be the summary sheet. Consider someone dragging sheets around or inserting a sheet or adding a PivotTable and that messe up the sheet order.

I almost always use the sheet Code Name rather than the sheet Name. the Code Name can only be modified in VB code of the VB editor. My convention would be wsSummary, for instance. Also Id code name the target sheets to sum ws1, ws2, ws3 etc and then in the code ONLY sume those sheets...

    For Each ws In ThisWorkbook.Worksheets
        With ws
            nSUM = Application.Sum(.Range("E2:E1000"))
            Select Case .CodeName
                Case "wsSummary"  'this might not ALWAYS be the first sheet
                Case "ws1", "ws2", "ws3" 
                    'This next line is just to place a total value spent on each individual sheet
                    .Cells(1, 6).Value = nSUM
                    'This next line is just to list all worksheet names onto Sumamry Sheet
                    wsSummary.Cells(lRow, 1).Value = .Name
                    'This next line is to place a total value spent on the summary sheet alongside each sheet name
                    wsSummary.Cells(lRow, 2).Value = nSUM
                    lRow = lRow + 1
            End Select
        End With
    Next ws

Also look for code that is duplicated, like your SUM spreadsheet function. Do it once, by assighning to a variable

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scribbler99]

If I am understanding this correctly I have placed Skips code into Module1

Then also

Private Sub Workbook_Open 
   Call SumSummary
End Sub


Private Sub Workbook_SheetChange event
    Call SumSummary
End Sub

But on opening the workbook it gets stuck in a loop

 

[Gruuuu]

Skip is a naughty coder! He forgot to disable events!

 

[SkipVought]

You should disable events BEFORE you call the code and enable AFTER it completes.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scribbler99]

Thanks Skip. That did the trick perfectly. Never mind Gruuuu's comment at least you asnwered my prayers.

 

[SkipVought]

Gruuuu's comment was valid. Recursiveness needs to be accounted for.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

scribbler, I was just teasing Skip a little.

I make the same exact mistake just about every time I monkey around with events, in fact I give myself a little hand slap when I do.

I have far less experience and expertise, and I do hope that Skip knows it came from a place of comraderie, instead of spite or malice!

 

[scribbler99]

Gruuuu

I did realise that and presume Skip did too. I just felt I needed to comment as a sign of gratitude for the help Skip had provided. I have been on the receiving end of Skips annoyance recently for not being clear in my descriptions/code so it's pleasing to know you are all only human after all.

Once again thanks guys you are all a great help to us novices.