Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Macros for copying and pasting
Macros for copying and pasting--I had a dos program that recognized arrow
keys and Home and End and you could paste anywhere, but Excel seems stuck on going back to the same spot to paste (by cell numbers). I want to copy from A1 and then go to B1 and paste and then be able to start on line C, <GoTo A1 Copy <End, Arrow down On C1 Paste |
#2
|
|||
|
|||
Just a guess: you recorded your cut and paste macro. This is an
excellent way to learn how to write macros! If you review the code, you'll see that it references the cells you used when recording the macro. The trick now is to alter the recorded code to make it do what you need it to do. As an example the macro refers to cell A1 as R[1]C[1] . You can change this to Range("a1").select If you need the column reference to stay static but change for the current row, change to code to Range("a" & selection.row).select Post your code, and indicate what you need the macro to do. |
#3
|
|||
|
|||
I think I've got it! Post it must mean, tell you what my macro says, so you
can debug it. I wanted it to paste a formula line by line so that when I wrote a receipt all I would have to do would be put in an item number and it would feed me the item description, price, tax, etc. (without cluttering the receipt by having formulas sitting there that were not being used). I discovered my biggest problem was that I needed to consider every starting point as an "a1" not some other letter. I hope that makes sense. This is my formula to copy: =VLOOKUP($D5,myin,2,FALSE) 1 =VLOOKUP($D5,myin,3,FALSE)*G5 =VLOOKUP($D5,myin,4,FALSE) This is my macro: (I think it is working) Sub CopyPaste() ' ' CopyPaste Macro ' Copy and Paste Formulas Line by Line ' ' Keyboard Shortcut: Ctrl+p ' Application.Goto Reference:="R5C5" ActiveCell.Range("a1:e1").Select Selection.Copy Selection.End(xlDown).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("a1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, -1).Range("a1").Select End Sub |
#4
|
|||
|
|||
Have a look at Ron de Bruin's site for several routines for copy/pasting.
http://www.rondebruin.nl/copy1.htm Also, when recording a macro, hit the "Relative Reference" button to prevent hard-coded cell addresses. Gord Dibben Excel MVP On Fri, 1 Jul 2005 13:14:02 -0700, "denny" wrote: Macros for copying and pasting--I had a dos program that recognized arrow keys and Home and End and you could paste anywhere, but Excel seems stuck on going back to the same spot to paste (by cell numbers). I want to copy from A1 and then go to B1 and paste and then be able to start on line C, <GoTo A1 Copy <End, Arrow down On C1 Paste |
#5
|
|||
|
|||
Dave and Gordon both had helpful answers. The cite Gordon mentioned has a
lot of good stuff. I haven't made my way through it all, but it's got to be helpful. "denny" wrote: Macros for copying and pasting--I had a dos program that recognized arrow keys and Home and End and you could paste anywhere, but Excel seems stuck on going back to the same spot to paste (by cell numbers). I want to copy from A1 and then go to B1 and paste and then be able to start on line C, <GoTo A1 Copy <End, Arrow down On C1 Paste |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting data and macros | Excel Discussion (Misc queries) | |||
Copying and PAsting using MACROS | Excel Worksheet Functions | |||
Pasting numbers and formulas without pasting format. | Excel Discussion (Misc queries) | |||
lose formatting (border) in excel cell after pasting from word | Excel Discussion (Misc queries) | |||
Copy and pasting files with macros | Excel Discussion (Misc queries) |