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

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.

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

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

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