worksheet change macro not working

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
I have the following worksheet change macro that is placed in the worksheet called "Data":

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Address = "$B$16" Then Call Stratum1
End Sub

which should run the following macro

Sub Stratum1()
'
' Stratum1 Macro
' Macro recorded 25/11/2005 by Mark Gillis
' Copies and pastes the info for the stratum
'


'
Sheets("Stratum Header").Select
Range("A2:G34").Select
Selection.Copy
Sheets("Data").Select
Range("A16:G48").Select
ActiveSheet.Paste
End Sub

If i run the stratum1 macro it works fine, I just can't get it to run automatically when the cell B16 is "1".

What am I missing?

Mark
 
Thanks jsut_jon. I will admit that that code has lost me and not to sure how it all works. That said, I can't get it to work. I will try and understand it and figure out why it's not working for me
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Mark

Is the change event actually being triggered?

To find out set a breakpoint using F9 and enter a value on the worksheet.

If the code is being triggered you should be transferred to the VBA editor where you can step through the code using F8.
 
Upvote 0
Mark_G said:
Thanks jsut_jon. I will admit that that code has lost me and not to sure how it all works. That said, I can't get it to work. I will try and understand it and figure out why it's not working for me

The code I posted goes in the sheet module of the sheet holding B16

After adding it, change B16's value - does the copy/paste take place?
 
Upvote 0
Where exactly is the code located?

Have you any other code?

Have you turned off events using something like this, and forgot to turn them back on?
Code:
Application.EnableEvents = False
 
Upvote 0
Ok, so apparently I didn't turn things back on, although I didn't know I turned them off. Now the code errors on the following line:

Range("A" & i & "A" & i + 30).Copy Sheets("Data").Range("A16:G48")

And this is the bit I really do not understand
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Cells.Count = 1 And Target.Address = "$B$16" <SPAN style="color:#00007F">Then</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    i = ((Target.Value - 1) * 40) + 2
    Range("A" & i & "A" & i + 32).Copy Sheets("Data").Range("A16:G48")
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

First, revised/corrected code above.

The variable i gets its initial value from the value in cell B16.

When B16 is a one [ 1 ], its value becomes

i = (1 - 1) * 40 + 2 == 2

And hence i+32 = 34

So that range resolves to: Range("A2:A34")

which should be the range to be copied when B16 has a value of 1.

What error are you getting, or is it just not seeming to run?
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Cells.Count = 1 And Target.Address = "$B$16" <SPAN style="color:#00007F">Then</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    i = ((Target.Value - 1) * 40) + 2
    Range("A" & i & ":A" & i + 32).Copy Sheets("Data").Range("A16")
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

EDIT -- was my fault, missing :

Try this.
 
Upvote 0
Mark

What value is in B16 when you get the error?

Is there any protection on the worksheet?

Does this work any better?
Code:
Range("A" & i & "A" & i + 32).Copy Sheets("Data").Range("A16")
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top