Macro cut and paste quits, can't re-create

[NM Burton]

Can't figure out why the following stopped working -
worked for about 40 entries and then returned an error
msg, deleting and recreating then doesn't work properly.
I have a column of combined city state and zip, looks
like this: Chicago, IL 60606

I want to get the state and zip away from the city, and
put the zip in an adjacent column. I use the relative
toggle button when I record the macro. As I go down the
column, the macro puts the results in the current
(correct) cell locations, but it is replicating the city
and zip that were in the cell used when I recording the
macro.

I have gone into options to deactivate 'edit' and other
settings that may interfere. When recording the macro, I
use the tool bar buttons instead of shortcut keys ctrl-x
and ctrl-v, thinking that may be a problem. I have
rebooted and restarted with the original file, but it
seem the macro somehow corrupts after a little use and
can't get a new macro to work. Even took it home and
tried it in Excel 2003, and does the same thing there
(something get embedded in the spreadsheet that
conflicts?)

My recording process was to click the relative button,
start recording, name the macro, hit f2, hold down shift
while hitting left-arrow 5 times (highlites zipcode),
click on 'cut', backspace 5 times to delete the ", IL ",
tab twice to move over 2 columns, click on 'paste', hit
return which moves the active cell down a row and over to
one cell below my start point, and stop recording.
----------------------------------------------------------
-------------
-
Sub q()
'
' q Macro
' Macro recorded 11/24/2004 by N M Burton
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "Banks"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Range("A1").Select
End Sub
----------------------------------------------------------
-------------
Debug tells me the problem is with
the "ActiveSheet.Paste" command.

Any ideas?

Thanks. N Burton, Wisconsin

 

[Tom Ogilvy]

I don't see anything in your code that would do anything but put in the word
"banks" in the active cell and paste whatever is in the clipboard two cells
to the right, then move to the next cell below the original activecell.

So if you get an error where you say you do, that would indicate that the
clipboard is empty.