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
0
Utf
4/22/2010 1:31:02 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
826 Views

Similar Articles

[PageSpeed] 49

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" <Jock@discussions.microsoft.com> wrote in message 
news:1430AD19-0E8E-4D7B-B121-7E8DA27A7ACF@microsoft.com...
> 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 

0
Rick
4/22/2010 1:58:31 PM
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" <Jock@discussions.microsoft.com> wrote in message 
> news:1430AD19-0E8E-4D7B-B121-7E8DA27A7ACF@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
4/22/2010 3:03:01 PM
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" <Jock@discussions.microsoft.com> wrote in message 
news:40A3605B-9C4D-4722-BE59-CE67FF21C6C2@microsoft.com...
> 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" <Jock@discussions.microsoft.com> wrote in message
>> news:1430AD19-0E8E-4D7B-B121-7E8DA27A7ACF@microsoft.com...
>> > 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
>>
>> .
>> 
0
Rick
4/22/2010 5:45:08 PM
On Apr 22, 10:45=A0pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> 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 t=
he
> text got colored... manually or via Conditional Formatting (CF)? If it wa=
s
> colored manually, then you can do this...
>
> Range("C20").Value =3D Range("C16").Value
> Range("C20").Font.ColorIndex =3D Range("C16").Font.ColorIndex
>
> If, on the other hand, the color came from CF, then you will need to perf=
orm
> the same test(s) in code that you used in the CF and assign the appropria=
te
> ColorIndex as per the test result(s).
>
> --
> Rick (MVP - Excel)
>
> "Jock" <J...@discussions.microsoft.com> wrote in message
>
> news:40A3605B-9C4D-4722-BE59-CE67FF21C6C2@microsoft.com...
>
>
>
> > That's better.
> > It copies comments also - can I narrow it down so it only copies the te=
xt
> > 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 Co=
py
> >> 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" <J...@discussions.microsoft.com> wrote in message
> >>news:1430AD19-0E8E-4D7B-B121-7E8DA27A7ACF@microsoft.com...
> >> > The following line of code will copy data from one sheet (Wbs) to
> >> > another
> >> > (Wbd):
>
> >> > =A0 =A0Wbd.Range("C" & lngNewRow).Value =3D 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:=3Dxlpastevalues
Wbd.Range("C" & lngNewRow) .pastespecial paste:=3Dxlpasteformats

It is not required to know the format manual or Conditional,excel will
paste all formt.





0
Javed
4/23/2010 4:05:55 AM
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" <asadullajaved@gmail.com> wrote in message 
news:0634b2cb-24f0-4327-9323-db3c039b7158@y3g2000prb.googlegroups.com...
> On Apr 22, 10:45 pm, "Rick Rothstein"
> <rick.newsNO.S...@NO.SPAMverizon.net> 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" <J...@discussions.microsoft.com> wrote in message
>>
>> news:40A3605B-9C4D-4722-BE59-CE67FF21C6C2@microsoft.com...
>>
>>
>>
>> > 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" <J...@discussions.microsoft.com> wrote in message
>> >>news:1430AD19-0E8E-4D7B-B121-7E8DA27A7ACF@microsoft.com...
>> >> > 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.
>
>
>
>
> 
0
Rick
4/23/2010 5:06:57 AM
Reply:

Similar Artilces:

Conditionally format another cell?
Is there a way to change the background color of a cell based on the contents of a different cell? thanks!!! Just use a formula that refers to the other cell. If I want X99 to change depending on what's in A1. I select X99 and then format|conditional formatting Formula is: =$A$1="anythingyouwant" Robin wrote: > > Is there a way to change the background color of a cell based on the > contents of a different cell? > > thanks!!! -- Dave Peterson ...

Can't get 24 hour format to change to 12 hour in calendar
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: Exchange I have tried several times to change the International format setting to have a 12 hour am/pm set up in Entourage but the format still stays 24 hour in the Calendar display itself. Although the actual entries are in 12 hour format and Entourage uses the 12 hour format for everything else except how it shows the actual calendars. <br> Is there something else I need to do to have this display correctly in the calendars? <br> I'm using 2008 Web Services Edition. Love this produ...

Time Format.
I have a column with numbers that represent seconds, for example 109. How can I change this number (109) to this format, 0:01:49 Use =TIME(0,INT(I1/60),MOD(I1,60)) and format it as h:mm:ss -- HTH Bob "GEM" <GEM@discussions.microsoft.com> wrote in message news:072EE4E4-9BE0-4835-B246-79D1F4DFE977@microsoft.com... >I have a column with numbers that represent seconds, for example 109. > > How can I change this number (109) to this format, 0:01:49 =A1/(24*60*60) and format as [h]:mm:ss -- David Biddulph "GEM" <GEM@dis...

International date formats
When performing an Edit, replace on the cell value 10.09.03 (a system genereated date vale of 10th Sep 03) replaceing the . with /, Excel (and in VBA when recorded as a macro) sometimes changes the cell value to 09/10/03 (9th October 03). This is driving me potty and have spent a vast amount of time on it trying to work out what Excel is doing!!!! My Regional settings are set fro the UK. Any ideas anyone? "Mike" <michael.may@npower.com> wrote in message news:2d44701c39483$97f77730$a601280a@phx.gbl... > When performing an Edit, replace on the cell value > 10.09.0...

convert from .xls format to .csv format
Hi, Can anyone help me to convert .xls to .csv without changing the content. I want the leading zeros to remain in the csv. Thanks in advance M.Ravindran -- m_ravindran ------------------------------------------------------------------------ m_ravindran's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28668 View this thread: http://www.excelforum.com/showthread.php?threadid=483430 Hi take a look at the Text Write facility at http://www.tushar-mehta.com/excel/software/text_write_program/index.html Regards Roger Govier m_ravindran wrote: > Hi, >...

YYYY format displaying wrong date
I admit I'm confused by this. I'm using Excel '97 (Yes, yes, I know. Upgrade. But this is a client's system over which I have no control.) The client supplied me with a spreadsheet containing a lot of data. I need to make the data consistent for it to be usable. There is a column containing only the year which is currently defined as Text. I want the data to be Date type with a "YYYY" format. I managed to do this somehow in another spreadsheet of similar data. In the spreadsheet I'm currently working on, when I change the column to Date type with "YYY...

Area chart date format
I have an area chart that plots quarterly data. I want to show only years on the x-axis, so I changed the interval to 1 year and the number format to 'Custom yyyy'. However this is shortening my dates, so 2020 becomes 202. Bizarrely, 2021 and 2031 show up in full, but other dates are only shown by their first three digits. Any ideas? I suspect the space allotted for each label isn't quite long enough, so the last character is truncated. I know all digits are the same width, but sometimes, especially when the font size is small, "1" is narrower than the rest, whic...

Formatting Formulas
I have a series of formulas that creates English Language labels out of a variety of VLOOKUP's and the ever helpful & sign. Basically, I used the VLOOKUPS to get "Big", "Bag", "Delicious", and "Apples" in cells A1-D1, respectively. Then, in E1, I've got =A1&" "&B1&" of "&C1&" "&D1 so that it comes out Big Bag of Delicious Apples In E2 it says Small Bundle of Tart Grapes and E3 says Big Bundle of Tart Apples, etc. What I'd like to have, though, is some sort of formatting set u...

Format a single digit like "007"?
In the query result, I need to see (for example) a single digit number (say that "7") as "007". What is the function for this? I use FormatNumber function for other purposes. Can you help me please? try the following: format(7,"000") 007 format(77,"000") 077 format(777,"000") 777 "mezzanine1974" wrote: > In the query result, I need to see (for example) a single digit number > (say that "7") as "007". > What is the function for this? I use FormatNumber function for other > purposes. > Can you he...

Protection Formatting
Hi, Is it possible to protect only the format of any particular cell. Like border, shading, font etc.etc. Thus even if any valus is entered/ pasted into this cell, the original formatting will remain unaltered? Brgds, Kedar "Kedar J" <kedaris@yahoo.com> wrote in message news:094701c34b9c$2ddc4ea0$a601280a@phx.gbl... > Hi, > > Is it possible to protect only the format of any > particular cell. Like border, shading, font etc.etc. Thus > even if any valus is entered/ pasted into this cell, the > original formatting will remain unaltered? > > Brgds, &g...

Retainers
Does anyone know the process of receiving retainers and then applying them to the invoice. I need thenm to show up on the invoice. Any help is appreciated. Thank you, Brian In microsoft.public.money, bcarne wrote: >Does anyone know the process of receiving retainers and >then applying them to the invoice. I need thenm to show >up on the invoice. >Any help is appreciated. Not many Business users provide answers. ...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Format Cell #5
I have a spreadsheet that is used by multiple users. I need to see if there is a way to separate floor numbers and room numbers. Example: In column "D" there will be: 5012-2 6004 7012-3 6004-02 ICUD 3014a The floor number is the first number in the column. I need to see if this can be separated to 5th floor, 6th floor, ICU (which may also be listed as 3014, 3012 etc. or ICU). I need to have a formula to count the number that went to the 5th floor, 6th, etc. I thought that if there is a formula to enter to separate the floors instead of adding another column and having e...

Upgrading an .mdw to 2003 format?
I have upgraded my frontend & backend .mdb from 2000 to 2003. Do I also upgrade the .mdw in the same way? Thanks so much. Mary On Thu, 27 May 2010 16:38:01 -0700, sweeneysmsm <sweeneysmsm@discussions.microsoft.com> wrote: >I have upgraded my frontend & backend .mdb from 2000 to 2003. Do I also >upgrade the .mdw in the same way? Nope. Just use it. I'm using an Access97 MDW with no problems whatsoever. -- John W. Vinson [MVP] Thanks so much! Mary "John W. Vinson" wrote: > On Thu, 27 May 2010 16:38:01 -0700, swee...

Formatting
I am working in Microcsoft Publisher.I have typed in text and then inserted a picture. How do I get the text to wrap around the picture. When working in Word I could just insert a picture and the text could be wrapped around it. In publisher the text is confined to a text box. Whatever I seem to do the picture remains the same only the size of the text box alters and text will not wrap aound the picture.I have tried using edit points but have got in a real mudle I am desperate now. Any help please? What version Publisher? Are you sending the image to the front? Click the dog icon, (picture t...

Any way to set View format for all message views?
I have the standard Inbox folder plus about 20-30 other message folders where I store messages by subject. Is there any way that I can set up a custom View and apply it to ALL message folders in such a way that if I change that View (add or delete fields, change field width or order, etc.), all Views will be affected? Thanks -- Using Outlook 2000 on Windows 2000 LurfysMa <invalid@invalid.invalid> wrote: > I have the standard Inbox folder plus about 20-30 other message > folders where I store messages by subject. > > Is there any way that I can set up a custom View and a...

Lost formatting in e-mails composed in Outlook
When I compose e-mails using Outlook--either in Word or not in Word--the recipient receives the e-mail with all formatting stripped from it. I can't use color, different fonts, italics, etc., because all disappear between "Send" and "Receive" even when I send it to myself. I finally found and chose the option of NOT removing formatting when sending, no matter what program it is sent to, but it removes all formatting anyway. Anybody know how to make it leave all the formatting in e-mails, either in Word or not in Word, when sent? Also, anybody know how to turn off AL...

Save graph formatting
I have formatted my pivot graph but every time I refresh or change the pivot, the graph reverts back to a different default. I have saved it as a default graph. Have you tried unclicking the checkbox for 'AutoFormat table' in the PivotTable Options window? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Teresa" wrote: > I have formatted my pivot graph but every time I refresh or change the pivot, > the graph reverts back to a different default. I have saved it as a default >...

how to enter phone number in special format?
Hi, i created a form with a textbox where the user must enter his phone number, bank account number etc .... Does it exist a way to make the textbox with a specific format e.g. 'ddd/dddddd' (d = digit) for phone or 'ddd-dddddd-dd' for bank account number? Thanks for tips Luc Luc wrote: > i created a form with a textbox where the user must enter his phone > number, bank account number etc .... Does it exist a way to make the > textbox with a specific format e.g. 'ddd/dddddd' (d = digit) for > phone or 'ddd-dddddd-dd' for bank...

Migrating from Goldmine
Hi there, I'm migrating from Goldmine to CRM 4.0, and will ultimately have csv lists of accounts, contacts, leads, activities etc. In using either import method, it always fails when starting out, as if i import accounts first, when it tries to import the primary contact, since there are no contacts to lookup, it fails. Vice versa if i start by importing contacts, i cant automatically assign a parent customer, as the lookup fails because the accounts aren't imported yet. If i import accounts, then import contacts with parent customers listed, it works, but i've lost my ...

Text format as percentage
I have a userform that I use multiple times. When this form loads the first time, I use the following code to format the text to a percentage: Private Sub TextBox21_afterupdate() If TextBox21.Value <> "" Then TextBox21 = TextBox21 / 100 With UserForm1.TextBox21 .Text = Format(.Text, "0.00%") End With End If however, if I ever focus back on that textbox, and then focus off, it crashes the code. how would I add code that basically says "if the number is already in percentage format, ignore the rest of ...

Conditional Format-possible to show overridden formulas?
I've got a cell that has a formula in it and I'd like to set up a conditional format so that it turns a different color if someone instead overrides the formula and inputs a value - is this possible? Thanks You could have a UDF that checks if it is a formula, and use that in the CF Function IsFormula(rng As Range) As Boolean IsFormula = rng.HasFormula End Function and use like =IsFormula(A1) -- HTH RP (remove nothere from the email address if mailing direct) "GretOgrady" <gretchen.ogrady@ey.com> wrote in message news:1111425009.372156.107570@f14g2000cwb....

Retained Earnings posting issue
We have a customer who closed their fiscal year as of Sep. 30, 2008. They made some adjusting journal entries over the weekend and posted the batch. While attempting to reuse the same batch ID for additional entries, they found that one entry from the previous posting was still in the batch and the batch was in Batch Recovery. The edit list showed the error message that the Retained Earnings account was not a Balance Sheet account. We found that the RE account had been changed in GL setup. The problem is that of the six entries in this batch, 4 should not have posted due to this err...

Formatting in a Formula
Does anyone know a way I can add a fill color to a cell if the value exceeds a threshold (ie an alert)? Thanks, Hi Yes, use conditional formatting click in the cell in question choose format / conditional formatting choose cell value is choose greater than (or whatever) enter the value click on the format button set the formatting click Ok, and Ok again Cheers JulieD "Frank" <Frank@discussions.microsoft.com> wrote in message news:CADB9F74-4B0B-4724-A837-39C0871A50B4@microsoft.com... > Does anyone know a way I can add a fill color to a cell if the value exceeds > a ...

formating comments
Can anyone tell me how to change the default format for comments? You can change the font size and colour of new comments (but not the font) by changing the settings in your Control Panel. (Instructions for Windows XP) 1. Right-click on the desktop, and choose Properties 2. On the Appearance tab, click Advanced. 3. From the Item dropdown, choose Tooltip 4. Choose a font Size, click OK, click OK Note -- this will also affect your Tooltips formatting Or, you can use a macro to insert a comment with a specific font and format: Sub CommentAddOrEditTNR() 'adds TimesNewRoman comment or posi...