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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
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