Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a cell reference in a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a cell reference in a macro
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 -- HTH Bob Phillips "Momo" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a cell reference in a macro
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? "Bob Phillips" wrote: 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 -- HTH Bob Phillips "Momo" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a cell reference in a macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a cell reference in a macro
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? -- HTH Bob Phillips "Momo" wrote in message ... 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? "Bob Phillips" wrote: 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 -- HTH Bob Phillips "Momo" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a cell reference in a macro
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 " wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a cell reference in a macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro using a cell reference | Excel Discussion (Misc queries) | |||
Cell Reference Macro | Excel Discussion (Misc queries) | |||
Macro cell reference help | Excel Programming | |||
Cell Reference in a Macro | Excel Programming | |||
run a macro from a cell reference | Excel Programming |