Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
I am trying to learn about what macros can and can't do.
Essentially, I want to copy data from a fixed location and paste it in a relative location. It seems like this should be possible, but no matter what I try, it fails. Most recently, I have tried to use two separate macros. Macro 1 selects and copies the fixed data. Selection.Copy Macro 2 should paste two spaces to the right of where the cursor is. ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste I have recorded these macros, but when I try to run Macro 2, I get an error (either 1004 or 438 if that is relevant) Is what I am trying to do possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
Hi Uturnabout,
Try: '<<======== Public Sub Tester() With Selection .Copy ActiveSheet.Paste Destination:=.Offset(0, 2) Application.CutCopyMode = False End With End Sub '<<======== or: '<<======== Public Sub Tester() With Selection .Copy Destination:=.Offset(0, 2) End With End Sub '<<======== --- Regards. Norman "uturnabout" wrote in message ... I am trying to learn about what macros can and can't do. Essentially, I want to copy data from a fixed location and paste it in a relative location. It seems like this should be possible, but no matter what I try, it fails. Most recently, I have tried to use two separate macros. Macro 1 selects and copies the fixed data. Selection.Copy Macro 2 should paste two spaces to the right of where the cursor is. ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste I have recorded these macros, but when I try to run Macro 2, I get an error (either 1004 or 438 if that is relevant) Is what I am trying to do possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
Hi
This will do roughly what you're trying to Range("a1").Select ActiveCell.Copy Range("a1").Offset(0, 2).Activate ActiveCell.PasteSpecial so will Range("a3").Select ActiveCell.Copy Range("a3").Offset(0, 2).PasteSpecial a better approach is Range("a3").Copy Range("a3").Offset(0, 2).PasteSpecial The "active" property doesn't support "paste" you have to use "pastespecial" (and "activesheet" is definately the wrong object :). Using "active" and "select" tends to slow down the program a very great deal and is best avoided unless you really need it. hth Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
You can do it in 1 macro. Actually you can do it with one line of code...
Range("A1:A10").Copy Destination:= Activecell.offset(0,2) Change the A1:A10 to suit. -- HTH... Jim Thomlinson "uturnabout" wrote: I am trying to learn about what macros can and can't do. Essentially, I want to copy data from a fixed location and paste it in a relative location. It seems like this should be possible, but no matter what I try, it fails. Most recently, I have tried to use two separate macros. Macro 1 selects and copies the fixed data. Selection.Copy Macro 2 should paste two spaces to the right of where the cursor is. ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste I have recorded these macros, but when I try to run Macro 2, I get an error (either 1004 or 438 if that is relevant) Is what I am trying to do possible? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
With Selection
.Copy .Offset(, 2).PasteSpecial xlPasteAll End With Application.CutCopyMode = False -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "uturnabout" wrote in message ... I am trying to learn about what macros can and can't do. Essentially, I want to copy data from a fixed location and paste it in a relative location. It seems like this should be possible, but no matter what I try, it fails. Most recently, I have tried to use two separate macros. Macro 1 selects and copies the fixed data. Selection.Copy Macro 2 should paste two spaces to the right of where the cursor is. ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste I have recorded these macros, but when I try to run Macro 2, I get an error (either 1004 or 438 if that is relevant) Is what I am trying to do possible? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
To all,
I apologize. I see now, I was not clear in the explanation of my problem. I want to copy data always from cells C5-G5 I want to paste data in a changing row, identified by selecting the corresponding cell in column A (i.e. select A15, means paste to C15-G15) Thanks for your quick replies. "uturnabout" wrote: I am trying to learn about what macros can and can't do. Essentially, I want to copy data from a fixed location and paste it in a relative location. It seems like this should be possible, but no matter what I try, it fails. Most recently, I have tried to use two separate macros. Macro 1 selects and copies the fixed data. Selection.Copy Macro 2 should paste two spaces to the right of where the cursor is. ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste I have recorded these macros, but when I try to run Macro 2, I get an error (either 1004 or 438 if that is relevant) Is what I am trying to do possible? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
Thanks Jim, That one works.
I thought the act of copying would change the selection. kudos for deciphering my imprecise question. "Jim Thomlinson" wrote: You can do it in 1 macro. Actually you can do it with one line of code... Range("A1:A10").Copy Destination:= Activecell.offset(0,2) Change the A1:A10 to suit. -- HTH... Jim Thomlinson "uturnabout" wrote: I am trying to learn about what macros can and can't do. Essentially, I want to copy data from a fixed location and paste it in a relative location. It seems like this should be possible, but no matter what I try, it fails. Most recently, I have tried to use two separate macros. Macro 1 selects and copies the fixed data. Selection.Copy Macro 2 should paste two spaces to the right of where the cursor is. ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste I have recorded these macros, but when I try to run Macro 2, I get an error (either 1004 or 438 if that is relevant) Is what I am trying to do possible? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed and Relative Macro References
When you record macros you will see selections all over the place. That is
because you are recording your actions and you are making selections. But there is almost no need for a macro to do any selecting. You can refer directly to workbooks, sheets and ranges. That makes your macros shorter faster and less prone to failure. -- HTH... Jim Thomlinson "uturnabout" wrote: Thanks Jim, That one works. I thought the act of copying would change the selection. kudos for deciphering my imprecise question. "Jim Thomlinson" wrote: You can do it in 1 macro. Actually you can do it with one line of code... Range("A1:A10").Copy Destination:= Activecell.offset(0,2) Change the A1:A10 to suit. -- HTH... Jim Thomlinson "uturnabout" wrote: I am trying to learn about what macros can and can't do. Essentially, I want to copy data from a fixed location and paste it in a relative location. It seems like this should be possible, but no matter what I try, it fails. Most recently, I have tried to use two separate macros. Macro 1 selects and copies the fixed data. Selection.Copy Macro 2 should paste two spaces to the right of where the cursor is. ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste I have recorded these macros, but when I try to run Macro 2, I get an error (either 1004 or 438 if that is relevant) Is what I am trying to do possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro using relative references | Excel Worksheet Functions | |||
Relative references and macro programming | Excel Programming | |||
macro vba relative references | Excel Worksheet Functions | |||
relative cell references in vba macro | Excel Programming | |||
Making a macro references relative | Excel Programming |