Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying with transpose and VB code
I have 3 Q's that I require help with.
I want to copy a row of information from one worksheet to another using the transpose, as the copy needs to run down a column on the sheet I'm copying to, but it always returns an absolute column and relative row, instead of relative column absolute row! Q. How do I get it to return relative column and absolute row? Q. Is there a way to set all cell references within a worksheet to absolute cell references in 1 fell swoop instead of having to do each cell individually? Q. Can anyone give me some VB code to do the following in a macro, Increment the row, column and range references by 1, then repeat the code incrementing the row, column and range ref's each time for a set number of times (442 total), then end the code/macro. I started the code below, which works and I can copy it, but have to edit every line and 442 times is a bit much! ActiveCell.FormulaR1C1 = "='Shift plan'!R[-2]C[3]" Range("C7").Select ActiveCell.FormulaR1C1 = "='Shift plan'!R[-3]C[4]" Range("C8").Select Thanks to all in anticipation, Keith |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying with transpose and VB code
for Rowcounnt = 6 to 442 Range("C" & RowCount).formulaR1C1 = "='Shift plan'!R4C[3]" next Rowcount "keith" wrote: I have 3 Q's that I require help with. I want to copy a row of information from one worksheet to another using the transpose, as the copy needs to run down a column on the sheet I'm copying to, but it always returns an absolute column and relative row, instead of relative column absolute row! Q. How do I get it to return relative column and absolute row? Q. Is there a way to set all cell references within a worksheet to absolute cell references in 1 fell swoop instead of having to do each cell individually? Q. Can anyone give me some VB code to do the following in a macro, Increment the row, column and range references by 1, then repeat the code incrementing the row, column and range ref's each time for a set number of times (442 total), then end the code/macro. I started the code below, which works and I can copy it, but have to edit every line and 442 times is a bit much! ActiveCell.FormulaR1C1 = "='Shift plan'!R[-2]C[3]" Range("C7").Select ActiveCell.FormulaR1C1 = "='Shift plan'!R[-3]C[4]" Range("C8").Select Thanks to all in anticipation, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting and Copying Marco code | Excel Discussion (Misc queries) | |||
Transpose a text string while copying adjacent column data to new | Excel Worksheet Functions | |||
Copying code | New Users to Excel | |||
How do I Transpose without copying duplicates? | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |