Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro cut and paste quits, can't re-create
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro cut and paste quits, can't re-create
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. -- Regards, Tom Ogilvy "NM Burton" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
1 Create a macro to Copy & paste certain data to another sheet | Excel Discussion (Misc queries) | |||
Excel quits when saving Macro spreadsheets | Excel Discussion (Misc queries) | |||
I need formula help or create a macro to copy and paste value only | Excel Discussion (Misc queries) | |||
create a macro to copy/paste cell data | Excel Programming | |||
Create an undoable 'paste formula' macro | Excel Programming |