| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I retain formatting?
The following line of code will copy data from one sheet (Wbs) to another
(Wbd): Wbd.Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo) How can I adapt the above to retain the formatting from the source sheet? -- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I retain formatting?
The default property for a range is Value, so your statement is only
assigning one value to another cell's value. If you use the range's Copy method, then you bring everything about the cell over. Give this a try... wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow) -- Rick (MVP - Excel) "Jock" wrote in message ... The following line of code will copy data from one sheet (Wbs) to another (Wbd): Wbd.Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo) How can I adapt the above to retain the formatting from the source sheet? -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I retain formatting?
That's better.
It copies comments also - can I narrow it down so it only copies the text and the format the text is in (i.e bold or red)? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: The default property for a range is Value, so your statement is only assigning one value to another cell's value. If you use the range's Copy method, then you bring everything about the cell over. Give this a try... wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow) -- Rick (MVP - Excel) "Jock" wrote in message ... The following line of code will copy data from one sheet (Wbs) to another (Wbd): Wbd.Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo) How can I adapt the above to retain the formatting from the source sheet? -- Traa Dy Liooar Jock . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I retain formatting?
You have to be more specific when you ask your questions. Your original
message said... "How can I adapt the above to retain the formatting from the source sheet?" To answer the question you have now posed, you will have to tell us how the text got colored... manually or via Conditional Formatting (CF)? If it was colored manually, then you can do this... Range("C20").Value = Range("C16").Value Range("C20").Font.ColorIndex = Range("C16").Font.ColorIndex If, on the other hand, the color came from CF, then you will need to perform the same test(s) in code that you used in the CF and assign the appropriate ColorIndex as per the test result(s). -- Rick (MVP - Excel) "Jock" wrote in message ... That's better. It copies comments also - can I narrow it down so it only copies the text and the format the text is in (i.e bold or red)? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: The default property for a range is Value, so your statement is only assigning one value to another cell's value. If you use the range's Copy method, then you bring everything about the cell over. Give this a try... wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow) -- Rick (MVP - Excel) "Jock" wrote in message ... The following line of code will copy data from one sheet (Wbs) to another (Wbd): Wbd.Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo) How can I adapt the above to retain the formatting from the source sheet? -- Traa Dy Liooar Jock . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I retain formatting?
On Apr 22, 10:45*pm, "Rick Rothstein"
wrote: You have to be more specific when you ask your questions. Your original message said... "How can I adapt the above to retain the formatting from the source sheet?" To answer the question you have now posed, you will have to tell us how the text got colored... manually or via Conditional Formatting (CF)? If it was colored manually, then you can do this... Range("C20").Value = Range("C16").Value Range("C20").Font.ColorIndex = Range("C16").Font.ColorIndex If, on the other hand, the color came from CF, then you will need to perform the same test(s) in code that you used in the CF and assign the appropriate ColorIndex as per the test result(s). -- Rick (MVP - Excel) "Jock" wrote in message ... That's better. It copies comments also - can I narrow it down so it only copies the text and the format the text is in (i.e bold or red)? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: The default property for a range is Value, so your statement is only assigning one value to another cell's value. If you use the range's Copy method, then you bring everything about the cell over. Give this a try.... wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow) -- Rick (MVP - Excel) "Jock" wrote in message ... The following line of code will copy data from one sheet (Wbs) to another (Wbd): * *Wbd.Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo) How can I adapt the above to retain the formatting from the source sheet? -- Traa Dy Liooar Jock .- Hide quoted text - - Show quoted text - Dear Jock Try this wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow) .pastespecial paste:=xlpastevalues Wbd.Range("C" & lngNewRow) .pastespecial paste:=xlpasteformats It is not required to know the format manual or Conditional,excel will paste all formt. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I retain formatting?
Hmm, you know Javed, you may be on to something here.<g For some reason, I
was going for a manual coloring of the text in the "copied" cell; but, of course, imposing the same Conditional Formatting on the "copied" cell as existed in the original cell would have the same visual effect. I'm not sure why that little subtlety escaped me... Jock, do what Javed has suggested. Watch out for the extra space that crept into the two PasteSpecial statements in front of the dot for the PasteSpecial method calls. Also, you may want to follow Javed's code lines with this one in order to remove the "marching ants" around the cell being copied... Application.CutCopyMode = 0 -- Rick (MVP - Excel) "Javed" wrote in message ... On Apr 22, 10:45 pm, "Rick Rothstein" wrote: You have to be more specific when you ask your questions. Your original message said... "How can I adapt the above to retain the formatting from the source sheet?" To answer the question you have now posed, you will have to tell us how the text got colored... manually or via Conditional Formatting (CF)? If it was colored manually, then you can do this... Range("C20").Value = Range("C16").Value Range("C20").Font.ColorIndex = Range("C16").Font.ColorIndex If, on the other hand, the color came from CF, then you will need to perform the same test(s) in code that you used in the CF and assign the appropriate ColorIndex as per the test result(s). -- Rick (MVP - Excel) "Jock" wrote in message ... That's better. It copies comments also - can I narrow it down so it only copies the text and the format the text is in (i.e bold or red)? -- Traa Dy Liooar Jock "Rick Rothstein" wrote: The default property for a range is Value, so your statement is only assigning one value to another cell's value. If you use the range's Copy method, then you bring everything about the cell over. Give this a try... wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow) -- Rick (MVP - Excel) "Jock" wrote in message ... The following line of code will copy data from one sheet (Wbs) to another (Wbd): Wbd.Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo) How can I adapt the above to retain the formatting from the source sheet? -- Traa Dy Liooar Jock .- Hide quoted text - - Show quoted text - Dear Jock Try this wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow) .pastespecial paste:=xlpastevalues Wbd.Range("C" & lngNewRow) .pastespecial paste:=xlpasteformats It is not required to know the format manual or Conditional,excel will paste all formt. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to retain the formatting from a different part of a spreads | Excel Discussion (Misc queries) | |||
| Retain cell formatting? | Excel Programming | |||
| retain formatting when concatenating | Excel Worksheet Functions | |||
| retain formatting as an actual value | Excel Worksheet Functions | |||
| retain cell formatting | Excel Discussion (Misc queries) | |||
Linear Mode
