VBA Macro on two worksheets

[stevetrade]

I have a VBA object in an Excel 2003 worksheet that triggers three macros when values are attained via streaming data. It runs nicely. I would like to duplicate the worksheet, using different input data, and have both sheets run at the same time. I can have both sheets open now, but the macros only trigger on the worksheet that is currently selected. Each worksheet has the same object and macros in it.

The object monitors three cells, and when the value in I1 is attained, it triggers the “PasteFast” macro, next, when Q1 is attained, “PasteFast2” is triggered, next, when Y1 is attained, “PasteSlow” is triggered. Then the whole thing repeats.

I would love some help being able to run both worksheets at the same time.

Here is the VBA object:

Private SubWorksheet_Calculate()

   Static oldval1
   Static oldval2
   Static oldval3
   
   Static LastAction As Integer
   ' Initial state will be 0, neither Fast norSlow
   Const Fast As Integer = 1
   Const Fast2 As Integer = 2
   Const Slow As Integer = 3
   
   Application.EnableEvents = False
   
   If Range("I1").Value ="1" And oldval1 <> "1" And LastAction <> FastThen
      PasteFast
      LastAction = Fast
   ElseIf Range("Q1").Value ="1" And oldval2 <> "1" And LastAction <> SlowThen
      PasteFast2
      LastAction = Fast2
   ElseIf Range("Y1").Value ="1" And oldval3 <> "1" And LastAction <> SlowThen
      PasteSlow
      LastAction = Slow
   End If
   
   oldval1 = Range("I1").Value
   oldval2 = Range("Q1").Value
   oldval3 = Range("Y1").Value
      
   Application.EnableEvents = True
   
End Sub

And, here are the three macros – they are essentially the same – they copy from the same location, but paste into different locations. PasteSlow and PasteFast are on one module, andPasteFast2 is on a second module (for no reason).

Sub PasteSlow()
'
' PasteSlow Macro
'

'
    Application.ScreenUpdating = False
    Range("G5:G57").Select
    Selection.Copy
    Range("H5").Select
    Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K5:K57").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("L5").Select
    Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

Sub PasteFast()
'
' PasteFast Macro
'

'
    Application.ScreenUpdating = False
    Range("g5:g57").Select
    Selection.Copy
    Range("P5").Select
    Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("k5:k57").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T5").Select
    Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub


Sub PasteFast2()
'
' PasteFast2 Macro
'

'
    Application.ScreenUpdating = False
    Range("g5:g57").Select
    Selection.Copy
    Range("x5").Select
    Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("k5:k57").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("ab5").Select
    Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

 

[Norie]

Try using the Workbook level event SheetCalculate.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

That will be triggered whenever any sheet in the workbook is calculated.

The worksheet that has been changed is passed to the event as the argument Sh.

 

[stevetrade]

Try using the Workbook level event SheetCalculate.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

That will be triggered whenever any sheet in the workbook is calculated.

The worksheet that has been changed is passed to the event as the argument Sh.

Hello Norie - please forgive my formatting. I cannot figure out how to reply properly. I hope you can see this. Thank you for your reply. Where would I paste the code you suggested? Steve