Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

[Q.] How in a Macro to select range to paste in.

Posted by Carl Benker on November 16, 2000 3:53 PM

I am trying to use a macro to paste a formula in a
group of cells. I want to paste the formula in
Column A in Rows 1 through the row of the currently
select cell. How do I do this?? It seems like I
should be able to use "R1C1" references to do this
like:
Range("RC1:R1C1").Select
ActiveSheet.Paste
but it won't work. Help doesn't give much assistance
except to desribe R1C1 references.

Please help.
Carl

carl.h.benker@ucm.com

Check out our Excel VBA Resources

Re: [Q.] How in a Macro to select range to paste in.

Posted by Donald on November 16, 2000 5:35 PM

Try this:

ActiveCell.Select
Selection.Copy
Range(Selection, "A1").Select
ActiveSheet.Paste

Re: [Q.] How in a Macro to select range to paste in.

Posted by Carl Benker on November 17, 2000 8:51 AM

That doesn't quite do what I want. You are assuming that I
want to paste from the current cell to A1. I want to paste
from column A and the current row (current cell location is
not necessarily in column A) to A1. Think of it as the current
cell is C3, and I want to paste from A3 to A1. The current
cell could be on any row though (not necessarily row 3).

Re: [Q.] How in a Macro to select range to paste in.

Posted by Tim Francis-Wright on November 17, 2000 11:04 AM

There are a number of ways to specify the range
A1:A[current row]; one is

ActiveCell.Copy
Range("A1:A" & ActiveCell.Row).PasteSpecial

I think this is what you need.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.