Need vba macro to run in a specific sheet in the background

[rolando87]

Hey guys,

I have a macro that runs automatically when a cell is updated automatically via RTD links. So to make this system work I need the code to run in a specific sheet in the background regardless of what sheet I may be on at the moment. I have some of the code working but I still haven't solved the background specific sheet part. This is what I have maybe someone can do the slight editing required to make it work? Thank you

Note: this code is in the microsoft excel objects in Sheet2(15 min bars)

Public Prev_Val As Long
Private Sub Worksheet_Calculate()
Dim rngF3 As Range
Set rngF3 = Range("F2")
      If rngF3 <> Prev_Val Then
         Application.EnableEvents = False
         Application.ScreenUpdating = False
 
 
 With Sheets("15 Min Bars")
    .Rows ("4:4")
    .Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     With Sheets("15 Min Bars")
        .Range("B2:E2").Copy
     With Sheets("15 Min Bars")
        .Range ("A4")
        .Selection.PasteSpecial Paste:=xlPasteValues
        End With
        End With
  End With
 
    Application.CutCopyMode = False
          Application.ScreenUpdating = True
         Application.EnableEvents = True
       End If
End Sub

 

[rolando87]

I forgot to mention that its onyl when cell F2 changes value that the code should run.

The code I'm trying to execute in the background whne cell F2 changes is simply:

1. select row 4 and insert new row there
2. Copy Cells B2:E2
3. Paste that into row 4.

Repeat every time cell F2 changes again.

 

[rolando87]

UPDATE:

I've seemed to have almost solved it with the exception that anytime I change the value in ANY cell in sheet "15 mins bars", it executes the macro. Does anyone know what I'm missing in the code below to run the macro only when Cell F2 changes, not when any cell in that sheet changes?

Public Prev_Val As Long
Private Sub Worksheet_Calculate()
Dim rngF3 As Range
Set rngF3 = Range("F2")
      If rngF3 <> Prev_Val Then
         Application.EnableEvents = False
         Application.ScreenUpdating = False


With Sheets("15 Min Bars").Rows("4:4")
.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
With Sheets("15 Min Bars").Range("B2:E2")
        .Copy
  End With
With Sheets("15 Min Bars").Range("A4")
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  End With
    Application.CutCopyMode = False
          Application.ScreenUpdating = True
         Application.EnableEvents = True
       End If
End Sub