I didn't run your code but are macros enabled? In Excel 2007 they won't be
if the workbook has a File Open password or if it has Workbook protection.

Unrelated comments fwiw..

Run "CopyRtoZ"

If the sub is in the same workbook you can just call it by name:

CopyRtoZ

Also sub CopyRtoZ doesn't need to do any selecting:

Sub CopyRtoZ()
Range("R5:R6").Copy
Range("Z5").PasteSpecial Paste:=xlPasteValues
End Sub


--
Jim
wrote in message
oups.com...
| Can anyone tell me why this VBA doesn't work? - i'm sure i have
| managed to trigger macros before with the top code but it doesn't seem
| to work now.
|
| I have a data feed which updates 16 columns in excel (alternatively if
| i turn off the data feed, am i right in thinking the top code should
| run if i highlighted 16 columns and pressed delete?)
|
| In the code window for the sheet to which the data is going, i have
| the following:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
|
| If Target.Columns.Count < 16 Then Exit Sub
| Application.EnableEvents = False
|
| Run "CopyRtoZ"
|
| Application.EnableEvents = True
|
| End Sub
|
| In Module1 i have:
|
| Sub CopyRtoZ()
| '
| ' CopyRtoZ Macro
| '
| Range("R5:R6").Select
| Selection.Copy
| Range("Z5").Select
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
| End Sub
|
| I don't understand why the above does not trigger a copy R5 and R6 to
| Z5 and Z6.
|
| I know very little about VBA, i am using Excel 2007 and saving as
| a .xlsm file as this is what it tells me to do. The security options i
| can find are all on low.
|
| thanks for any help.
|