0

I tried running the private sub code:

Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("G3")
    If Not Intersect(target, Range("G3")) Is Nothing Then
    End If
End Sub

I am pulling a value from one sheet and putting it into another cell on a separate sheet. I want the VBA to run when the cell is automatically changed. When I tried the code above, nothing happened when I updated the cell.

99moorem
  • 1,955
  • 1
  • 15
  • 27
jlo
  • 9
  • 1
  • 7
  • 1
    try using the worksheet_change event not calculate – 99moorem Jun 01 '15 at 12:45
  • It doesn't let me even run that code though. It says that I need to create a macro and then it wants to use 'sub change' not 'private sub'. Will it work if I use just 'sub'? – jlo Jun 01 '15 at 12:48
  • events can not be run manually, they have to run when the event fires. so build the worksheet_change event then do F9 on a line of (this puts a break point in your code), then when the event fires the code will pause there for you to step through. This may also help you http://www.cpearson.com/excel/Events.aspx – 99moorem Jun 01 '15 at 12:51
  • 1
    Check http://stackoverflow.com/questions/13012426/excel-vba-events or http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change/415159#415159 – 99moorem Jun 01 '15 at 12:58
  • I just can't get the 'private sub' to work. It only lets me use the 'sub'. Thanks though! – jlo Jun 01 '15 at 13:01

1 Answers1

0

From the sounds of things the issue seems to be that you're trying to put the code in a Module like a regular macro. To have it run based on a worksheet event, you need to have the code in that worksheet's code window (in the VBA window, there's the "Microsoft Excel Objects" folder, inside is the list of worksheets, double-click the worksheet to open it's code).

Once you've opened the worksheet's code, at the top of the window you should see two drop-downs. The left one should show "(General)". In that drop-down select "Worksheet" (should be the only option).

In the drop-down to the right of that, select "Change". Then you need to validate the Target is the right cell (If Target.Address = "$<Column letter>$<row #>"). Inside that If statement is where you'd nest your code to copy the value to the second worksheet

JMichael
  • 569
  • 11
  • 28