Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Copy formats and formulas in vba?
The following statement copies the formulas in one range to another range of
cells. How can I also copy the formats of "ExpRow"? Thank you. .... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With .... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Copy formats and formulas in vba?
This will probably work...
r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With -- Rick (MVP - Excel) "DK" wrote in message ... The following statement copies the formulas in one range to another range of cells. How can I also copy the formats of "ExpRow"? Thank you. ... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With ... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Copy formats and formulas in vba?
I found a solution. However, if you can see a better way I'd be glad to see
it. You guys are so good the answers arrive before you send them. Thanks again for all you do. DK With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula .Range("ExpRow").EntireRow.Copy .Range(.Cells(1, 1), .Cells(r, c)).PasteSpecial Paste:=xlPasteFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End With "DK" wrote in message ... The following statement copies the formulas in one range to another range of cells. How can I also copy the formats of "ExpRow"? Thank you. ... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With ... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Copy formats and formulas in vba?
Hey! I didn't know that one could copy from to in that manner.
Just the one line within the With statment seems to do the entire job. .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) Thanks very much Rick. DK "Rick Rothstein" wrote in message ... This will probably work... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With -- Rick (MVP - Excel) "DK" wrote in message ... The following statement copies the formulas in one range to another range of cells. How can I also copy the formats of "ExpRow"? Thank you. ... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With ... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Copy formats and formulas in vba?
That single line copies the formula, but it puts the same formula in each
cell (all cell references are the same, they don't change in the same way as when you "copy a formula down")... you need the second line I posted to do that (it overwrites the "static" formulas with the "copy down" type of formulas)... I only used the Copy statement to quickly move the format into the cells where you wanted the formulas. -- Rick (MVP - Excel) "DK" wrote in message ... Hey! I didn't know that one could copy from to in that manner. Just the one line within the With statment seems to do the entire job. .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) Thanks very much Rick. DK "Rick Rothstein" wrote in message ... This will probably work... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With -- Rick (MVP - Excel) "DK" wrote in message ... The following statement copies the formulas in one range to another range of cells. How can I also copy the formats of "ExpRow"? Thank you. ... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Copy formats and formulas in vba?
Rick,
I don't know if it makes any difference that I'm using xl-2007. I checked the resulting formulas using just the one line of code and it seems to be ok. However I may keep the .Formula line just to make sure. Here is a small sample using only .Range("ExpRow").Copy .Range(.Cells(1, 1), ..Cells(r, c)). 1st row is part of "ExpRow" next rows follow below. The first formula is common to all records, the relative formulas refer to the list of records. = testby =Input!D16 =Input!E16 = testby =Input!D17 =Input!E17 = testby =Input!D18 =Input!E18 Thanks again. "Rick Rothstein" wrote in message ... That single line copies the formula, but it puts the same formula in each cell (all cell references are the same, they don't change in the same way as when you "copy a formula down")... you need the second line I posted to do that (it overwrites the "static" formulas with the "copy down" type of formulas)... I only used the Copy statement to quickly move the format into the cells where you wanted the formulas. -- Rick (MVP - Excel) "DK" wrote in message ... Hey! I didn't know that one could copy from to in that manner. Just the one line within the With statment seems to do the entire job. .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) Thanks very much Rick. DK "Rick Rothstein" wrote in message ... This will probably work... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With -- Rick (MVP - Excel) "DK" wrote in message ... The following statement copies the formulas in one range to another range of cells. How can I also copy the formats of "ExpRow"? Thank you. ... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With ... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Copy formats and formulas in vba?
I'm using XL2003 and what I explained was necessary to make it work on my
system. -- Rick (MVP - Excel) "DK" wrote in message ... Rick, I don't know if it makes any difference that I'm using xl-2007. I checked the resulting formulas using just the one line of code and it seems to be ok. However I may keep the .Formula line just to make sure. Here is a small sample using only .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)). 1st row is part of "ExpRow" next rows follow below. The first formula is common to all records, the relative formulas refer to the list of records. = testby =Input!D16 =Input!E16 = testby =Input!D17 =Input!E17 = testby =Input!D18 =Input!E18 Thanks again. "Rick Rothstein" wrote in message ... That single line copies the formula, but it puts the same formula in each cell (all cell references are the same, they don't change in the same way as when you "copy a formula down")... you need the second line I posted to do that (it overwrites the "static" formulas with the "copy down" type of formulas)... I only used the Copy statement to quickly move the format into the cells where you wanted the formulas. -- Rick (MVP - Excel) "DK" wrote in message ... Hey! I didn't know that one could copy from to in that manner. Just the one line within the With statment seems to do the entire job. .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) Thanks very much Rick. DK "Rick Rothstein" wrote in message ... This will probably work... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range("ExpRow").Copy .Range(.Cells(1, 1), .Cells(r, c)) .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With -- Rick (MVP - Excel) "DK" wrote in message ... The following statement copies the formulas in one range to another range of cells. How can I also copy the formats of "ExpRow"? Thank you. ... r = Range("MtrCounter").Value c = Range("ExpRow").Columns.Count With Sheet2 .Range(.Cells(1, 1), .Cells(r, c)) = Range("ExpRow").Formula End With ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column, paste special formulas & number formats doesn't work | Excel Discussion (Misc queries) | |||
Using VB Copy Entire Row but formulas and formats only no values | Excel Discussion (Misc queries) | |||
What's the best way to add a row and copy formulas and formats? | Excel Discussion (Misc queries) | |||
Looking to copy a worksheet with all formats and formulas without coping inputed data | Excel Discussion (Misc queries) | |||
How to have formulas and formats auto copy to new inserted rows | Excel Discussion (Misc queries) |