If you want to paste (as opposed to creating a referencing formula as you
attempt in your code), try this

iRow = 1
Counter = 14
Cells(Counter, "A").Copy Destination:=Worksheets("Sheet2").Cells(iRow,
"A")
iRow = iRow + 1
Counter = Counter + 14
Cells(Counter, "A").Copy Destination:=Worksheets("Sheet2").Cells(iRow,
"A")


etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Novice" wrote in message
...

I'm a beginner so this will be a stupid question. How do I use the
offset function with a counter as my row number? What I'm trying to do
is paste data from sheet 1 into sheet 2 using offset with the row
number increased by 14 each time. For example:

Counter =14
ActiveCell.FormulaR1C1="Offset(Sheet1!R[13]C[-1],Counter,0)"
Counter=Counter +14

It doesn't work. I realize I should use the Cells Function, but I do
not know how to do it.

The data in sheet 1 is in rows that are 14 rows apart in column A. I
want to post the data in sheet 2 directly underneath each other in one
column.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/