Auto Update vs Button Update

I have the following code to update a summary page from multiple worksheets, is it possible to have this auto update so that the users don't have to remember to click the button.

Sub Summary()
    Dim ws As Worksheet
    Dim DestSheet As Worksheet
    Dim j As Long       'row index on destination sheet
    Dim sRow As Long    'row index on source worksheet
    Dim nRow As Long    'last row index on source worksheet

    ActiveSheet.Unprotect

    Application.ScreenUpdating = False
    Set DestSheet = Worksheets("Summary")
    j = 9
    For Each ws In ActiveWorkbook.Worksheets
        Select Case UCase(ws.Name)
        Case "TEMPLATE", "DROPDOWN"     'Ignore these worksheets (list in all caps!)
        Case Else   'Copy data from all the rest
            nRow = ws.Cells(ws.Rows.Count, "U").End(xlUp).Row

            ws.Cells(6, 2).Copy
            DestSheet.Cells(j, 2).PasteSpecial xlPasteValues    'Store Name
            ws.Cells(7, 2).Copy
            DestSheet.Cells(j, 3).PasteSpecial xlPasteValues    'Store Number
            ws.Cells(6, 9).Copy
            DestSheet.Cells(j, 4).PasteSpecial xlPasteValues   'Square Footage
            ws.Cells(13, 16).Copy
            DestSheet.Cells(j, 6).PasteSpecial xlPasteValues     'Professional Fees
            ws.Cells(14, 16).Copy
            DestSheet.Cells(j, 7).PasteSpecial xlPasteValues     'Parts
            ws.Cells(15, 16).Copy
            DestSheet.Cells(j, 8).PasteSpecial xlPasteValues     'Freight
            ws.Cells(16, 16).Copy
            DestSheet.Cells(j, 9).PasteSpecial xlPasteValues     'Contract
            ws.Cells(17, 16).Copy
            DestSheet.Cells(j, 10).PasteSpecial xlPasteValues    'Other Cost
            ws.Cells(18, 16).Copy
            DestSheet.Cells(j, 11).PasteSpecial xlPasteValues    'Contingency
            ws.Cells(22, 5).Copy
            DestSheet.Cells(j, 15).PasteSpecial xlPasteValues    'Base $Variance
            ws.Cells(22, 7).Copy
            DestSheet.Cells(j, 16).PasteSpecial xlPasteValues    'Base SF Variance
            ws.Cells(22, 9).Copy
            DestSheet.Cells(j, 17).PasteSpecial xlPasteValues    'Base %Variance
            ws.Cells(23, 5).Copy
            DestSheet.Cells(j, 18).PasteSpecial xlPasteValues    'SD $Variance
            ws.Cells(23, 7).Copy
            DestSheet.Cells(j, 19).PasteSpecial xlPasteValues    'SD SF Variance
            ws.Cells(23, 9).Copy
            DestSheet.Cells(j, 20).PasteSpecial xlPasteValues    'SD %Variance
            ws.Cells(24, 5).Copy
            DestSheet.Cells(j, 21).PasteSpecial xlPasteValues    'DD $Variance
            ws.Cells(24, 7).Copy
            DestSheet.Cells(j, 22).PasteSpecial xlPasteValues    'DD SF Variance
            ws.Cells(24, 9).Copy
            DestSheet.Cells(j, 23).PasteSpecial xlPasteValues    'DD %Variance
            ws.Cells(25, 5).Copy
            DestSheet.Cells(j, 24).PasteSpecial xlPasteValues    'FC $Variance
            ws.Cells(25, 7).Copy
            DestSheet.Cells(j, 25).PasteSpecial xlPasteValues    'FC SF Variance
            ws.Cells(25, 9).Copy
            DestSheet.Cells(j, 26).PasteSpecial xlPasteValues    'FC %Variance
            ws.Cells(19, 11).Copy
            DestSheet.Cells(j, 27).PasteSpecial xlPasteValues    'Incremental Total
            j = j + 1

            If j > 22 Then
                Rows(j + 1).Insert
                Rows(j).Copy
                Rows(j + 1).PasteSpecial Paste:=xlPasteFormats
            End If

        End Select
    Next

    Range("SumTC").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
        .PatternTintAndShade = 0
    End With




    Range("SumCtr").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With

    Range("SumFeesFmt").Select
    Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"

    ActiveSheet.Protect

End Sub

You can call your Summary macro from any number of Events ... the question is which event to choose.
The most commonly used are WorksheetActivate, Auto_Open, SelectionChange

If you want to update automatically when the workbook opens then try this in a code module

Sub Auto_open()
     Summary
End Sub

Selection_change is triggered when a cell selection is changed.
Worksheet_Change is triggered when a worksheet value is changed (except by formula)
Sub Auto_Open is when the workbook is opened Sub Auto_Close when closed
Workbook_beforesaveas - as part of the save routine.

Yes everything Except the Sub/End Sub lines should be inside the Worksheet_Change event

Got to the worksheet module module
Select Worksheet in the top left drop down
Select "Change" in the right hand dropdown

Put the code above between the sub and end sub of worksheet_change

That's probably because it's selecting multiple cells which is then triggering the error in the worksheet_change (being multicell)..

Try putting this in the code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Rows > 1 Or Target.Columns > 1 Then Exit Sub

Hi, shymac,

Your opening post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Ciao,
Holger

Hi,

Added:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows > 1 Or Target.Columns > 1 Then Exit Sub

And it error at the same line? Adding workbook added to the last two worksheets before the Summary tab.

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.screenupdating = false
Application.calculation = xlmanual
     ' Main body of code
Application.screenupdating = true
Application.calculation = xlautomatic
End Sub

shymac,

You were asked to put code tags in post 9.

I have added code tags to your 1st post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.