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

rolando87

New Member
Joined
Mar 13, 2017
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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)

VBA Code:
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

rolando87

New Member
Joined
Mar 13, 2017
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 13, 2017
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,860
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top