Fixed and Relative Macro References

[uturnabout]

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?

 

[Norman Jones]

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
'<<========

 

[Keith74]

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

 

[Jim Thomlinson]

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.

 

[Tim Zych]

With Selection
.Copy
.Offset(, 2).PasteSpecial xlPasteAll
End With
Application.CutCopyMode = False

 

[uturnabout]

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]

Thanks Jim, That one works.

I thought the act of copying would change the selection.

kudos for deciphering my imprecise question.

 

[Jim Thomlinson]

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.