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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Your event code says nothing about B16 being equal to 1;

What it says is

1] if there is a recognised change event, and

2] only a single cell was changed, and

3] that cell was B16, then

4] execute this other code

How is B16 changing?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Mark

Where is the code located?

The worksheet change code should be in a worksheet module.
Code:
Sub Stratum1() 

Sheets("Stratum Header").Range("A2:G34").Copy Sheets("Data").Range("A16:G48")

End Sub
 

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
THanks for the quick replies

THe stratum1 code is placed in a worksheet module. THe bell B16 is changing from being any number between 1 aned 10. What I plan one doing is if it is 1, it will run macro stratum1, it it is 2, it will run macro stratum2, and so on. Once I figure out how to make it work for stratum1, then it is just a matter of changing the range that is being copied.

What should the code say so that it runs stratum1 when b16 equals 1
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Mark

How is the number in B16 being changed/entered?

Is it the result of a formula?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Give us an idea of what the other macros do; there may be a way to combine them all in one, if the copy/paste ranges move in a predictable pattern.

BTW, still need to know how B16 is changed - manually/formula/etc

Also, change event code MUST go in the sheet module of which it monitors.
 

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
As for the other macros, they will be copying from a different range, but pasting to the same location as stratum1 macro does. The range they are copying from simplies moves down 40 rows everytime, ie

stratum1 = a2:g34
stratum2 = a42:g74
stratum3 = a82:g114
this pattern repeats all the way down to stratum10
 

JONESY

Board Regular
Joined
Jul 23, 2004
Messages
63
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Is it as simple as the above?

Thats in the ThisWorkbook


L
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
<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) + (Target.Value - 1) * 40
    Range("A" & i & "A" & i + 30).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>

Untested code above.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,536
Messages
5,572,774
Members
412,482
Latest member
arooshrana2
Top