Using a cell reference in a macro

[Guest]

Hi,

I would like to use a cell reference in the following Macro

'
Sheets("PA Journal").Select
Range("A2:K2").Select
Selection.Copy
Range("A3:??").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Data Entry Sheet").Select
'
Within the range("A3:??") i would like the question marks to be a reference
to a cell on the Data Entry Sheet which in turn would then return a value
from the cell of say K10 to complete the Range of cells to be copied,

Any suggestions

Thanks

Andy

 

[Bob Phillips]

Momo,

You don't need the full range, just the start cell

Sheets("PA Journal").Select
Range("A2:K2").Copy
Worksheets("Data Entry").Activate
Range("A3").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Guest]

Sorry, I think you miss undersatnd what I want to do,
I have a row with formula on it, this formula takes data which is manually
input into another sheet, (Sheet 2), but by using the macro which I
origonally posted, i hoped to return from a particular cell, a cell such as
k12 so that the formula would be coppied from 1 row to 12 rows rather than
having a sheet with thousands of rows of formula permantently there,

Any suggestions please?

 

[nnchoudhari]

Hi Andy,

try using following code

Range("A3:" & Range("k10").Value).Select

just be aware that the cells in new range are in multiple of the cells
in original range.

Regards
NC

 

[Bob Phillips]

So you are saying that you want to get a range of cells from the Sheet 'PA
Journal', and copy them to some rows on the Data Entry sheet?

How do you know where to start and where to finish copying these cells?

 

[Guest]

That last tip worked thanks, but just one more thing, I would like that
referenced cell (as put "K10") to be referenced from another sheet, so the
value in k10 would be in sheet 2, but the start "A3" is on sheet 1 ? is this
possible?

Thanks
Andy

 

[NC]

hi andy,
sorry could reply earlier
but i think you must have found the solution
any way the code is as below
replace "sheet1" with the sheetname on which your k10 is.
Range("A3:" & Sheets("sheet1").Range("k10").Value).Select

Regards
NC