Run Module Automatically When Excel Recalculates

[bkanne]

I have the following simple piece of code:

Sub Paste_Table_Values()
'
' Paste_Table_Values Macro
'
    Range("T28:X32").Select
    Range("X28").Activate
    Selection.Copy
    Range("T38").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

I currently have this assigned to a button, and when I make certain changes to my spreadsheet, I need to click the button to make the model work.

My question is - how can I avoid the step of having to use the button? Said a different way, how can this code re-run every time the workbook calculates? I would like it to run in the background (so it doesn't navigate back to the sheet this code affects everytime it updates).

Can someone please help me here? Thanks so much!

 

[Kenneth Hobson]

Do you mean copy/paste that range for every sheet or just one?

I would normally use the Worksheet Change event but it depends on your data (manual input changes vs. formulas) and needs.

If for just one sheet, right click the sheet's tab, View Code and paste one of these two.

Private Sub Worksheet_Calculate()
  Dim calc As Integer
 
  With Application
    calc = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
  End With

  Range("T28:X32").Copy
  Range("T38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
   
  With Application
    .Calculation = calc
    .EnableEvents = True
    .ScreenUpdating = True
    .CutCopyMode = False
  End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim calc As Integer, r As Range
 
  Set r = Intersect(Target, Range("T28:X32"))
  If r Is Nothing Then Exit Sub
 
  With Application
    calc = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
  End With

  Range("T28:X32").Copy
  Range("T38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
   
  With Application
    .Calculation = calc
    .EnableEvents = True
    .ScreenUpdating = True
    .CutCopyMode = False
  End With
End Sub

 

[bkanne]

Thanks so much for this.

The first piece of code you presented works fantastically, except that when I am on that worksheet, I can work anywhere else on the same sheet without the selections moving (as specified by the instructions in the code). Said differently, if I'm working in an unrelated area and press calculate, it moves me to the range that is being copied and pasted, away from the area I'm actually working.

Is there something I can do to prevent this from happened? Maybe change the code so that the instructions don't require actually selecting the code physically but the actions are still performed?

 

[bkanne]

I ended up re-writing like this:

Private Sub Worksheet_Calculate()
 
  Dim rngStart As Range
Set rngStart = ActiveCell

 
  Dim calc As Integer
  With Application
    calc = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
  End With

  Range("Copy_Range").Copy
  Range("Paste_Range").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
 
  With Application
    .Calculation = calc
    .EnableEvents = True
    .ScreenUpdating = True
    .CutCopyMode = False
  End With

    rngStart.Select

End Sub

Thanks again for the help!!

 

[Kenneth Hobson]

Try to avoid select, activate, selection and such if you can.

If values is all you need, this should suffice.

Sub Test()
  Dim r As Range
  Set r = Range("T28:X32")
  Range("T38").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
End Sub