worksheet change macro not working

[Mark_G]

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

 

[Mark_G]

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

 

[Norie]

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.

 

[just_jon]

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?

 

[Mark_G]

I tired the breakpoint and nothing happened. Itdoes not seem to be triggering anything

 

[Norie]

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?

Application.EnableEvents = False

 

[Mark_G]

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

 

[just_jon]

<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?

 

[Mark_G]

I am getting a

run time error '1004'
Method 'Range' of object '_worksheet' failed

Mark

 

[just_jon]

<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.

 

[Norie]

Mark

What value is in B16 when you get the error?

Is there any protection on the worksheet?

Does this work any better?

Range("A" & i & "A" & i + 32).Copy Sheets("Data").Range("A16")