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 for all the help. Not it seems to be working but I am getting an error message saying it can't change part of a merged cell.

I have gone through the whole range and there is no merged cells, either in the range being copied or the range it is posted into.

 

[Norie]

Are you 100% sure there are no merged cells?

Try selecting all the cells by clicking in the top left and the going to Format>Cells...

On the alignment tab is Merge Cells selected at all?

 

[Mark_G]

There is definitely no merged cells in the worksheet it is copying from, but on the one it is pasting too ther is merged cells, but these are all outside of the range it is posting too. There is in fact lots, but it is a lot of work to unmerge them all and clean the page back up. The problem is I am working with a workbook that someone else has created and do not want to redesign the entire thing. If there is no merged cells in the range it is pasting to, why won't it work.

 

[Norie]

Mark

There probably are merged cells in the range you are pasting to.

That is the only reason I can think of why you would get that error message.

Merged cells should generally be avoided, especially if you are using code. They just cause trouble, as you are seeing.

You could use the Centre Across Selection horizontal alignment instead

 

[Mark_G]

I got it to work for me. It was my not know how the code was working that was causing all the trouble. Now that I have spent some time trying to figure it out, there was a few small changes that were required to make it work for my situation. It is now running just like I wanted it to. Thanks so much for your help.

Here is the final code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Address = "$B$16" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim i As Integer
i = ((Target.Value - 1) * 40) + 2
Sheets("Stratum Header").Range("A" & i & ":G" & i + 32).Copy Sheets("Data").Range("A16")
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = False
End If
End Sub

Thanks again

Mark