copy with format

[horst]

I have a sheet with various formattings (background and text colored,
large and small texts, borders etc.).
When I copy and paste it into another sheet I get the text only and all
formattings are lost.
Paste special has only text and unicode.
Is there any way to copy everything?
Thanks
Horst

 

[Epinn]

Do you want to copy to a brand new sheet or an existing sheet?

To a brand new sheet, I use edit > move or copy sheet and then tick create a copy. This way I get all the formatting.

To an existing sheet, I do copy and paste and I get the formatting as well. If I do paste special, then I don't get the formatting. But I can use the format painter to finish the job. In case you need more info on format painter, Help has the details.

I use V 2003.

Hope this helps.

Epinn

I have a sheet with various formattings (background and text colored,
large and small texts, borders etc.).
When I copy and paste it into another sheet I get the text only and all
formattings are lost.
Paste special has only text and unicode.
Is there any way to copy everything?
Thanks
Horst

 

[horst]

Dear Epinn,

thank you very much: it was just in front of my eyes!

But if I copy and paste to an existing sheet I loose all formattings
(sizes, borders etc.)
The small image "Paste options" gives me only the choices of using
destination formats or Text Import Wizard.
What I miss is the use of source formats.
I have v2002.
Horst

 

[horst]

Dear Epinn,

thank you very much: it was just in front of my eyes!

But please try to explain me exactly what I should do in order to use
the Format Painter.
I made several tests, but haven't been able to copy the formats.
(e.g. select source range, click Format Painter Icon, click on
destination first cell, click Ctrl-V ????)
Horst

 

[Epinn]

I didn't paste the instructions from Help to the post because I tried to save storage.

But now I have found a link that can help you. If you click on the following link, you should be able to see the action. I like MS demos. Hope you like it too.

http://office.microsoft.com/en-us/assistance/HA012176101033.aspx

If you have any more questions, feel free to post again.

Good luck!

Epinn

Dear Epinn,

thank you very much: it was just in front of my eyes!

But please try to explain me exactly what I should do in order to use
the Format Painter.
I made several tests, but haven't been able to copy the formats.
(e.g. select source range, click Format Painter Icon, click on
destination first cell, click Ctrl-V ????)
Horst

 

[Epinn]

select source range, click Format Painter Icon, click on
destination first cell, click Ctrl-V ????)

Click on destination first cell and drag.

Epinn

Dear Epinn,

thank you very much: it was just in front of my eyes!

But please try to explain me exactly what I should do in order to use
the Format Painter.
I made several tests, but haven't been able to copy the formats.
(e.g. select source range, click Format Painter Icon, click on
destination first cell, click Ctrl-V ????)
Horst

 

[horst]

Thank you very much.
I have now been able to use Format Painter in Word, but not in Excel!
I drag on the destination cells, but nothing changes!
Any additional hints, please?
Horst

 

[Epinn]

Did you watch the demo (i.e. the link I included in the post)? Did it not help?

Actually, just clicking the first destination cell and without dragging works for me. I use V 2003.

Do you want to see if you can at least do the following?

Start a new sheet. Select A1:A10, click the fill color icon; any color is fine. So A1:A10 is filled with a color.

While A1:A10 is still *selected*, click on the format painter icon.

Then position the cursor (paint brush) in D1, click once. Did you get D110 filled with the same color?

Epinn

Thank you very much.
I have now been able to use Format Painter in Word, but not in Excel!
I drag on the destination cells, but nothing changes!
Any additional hints, please?
Horst

 

[horst]

I followed your advices, position the cursor with the paint brush in D1,
click and... nothing happens (only the brush disappears)!
I have version 2002 SP2
???
Horst

 

[Epinn]

Based on the following, Format Painter in Excel 2002 should work the same.

"The Format Painter in Excel 2002
Excel lets you use the Format Painter as well. Click the cell containing the formatting you want to use, click the Format Painter icon, and click one cell or drag a range of cells to duplicate the formatting. Cell formatting, in this case, includes character attributes, number formats, alignment, borders, patterns, and protection."

Do you want to try to add another Format Painter icon to the toolbar and see what happens?

Click Tools > Customize > Commands > Format, then scroll down on the RIGHT side until you see Format Painter. Click and drag it to the toolbar.

Now try to use this newly added format painter and see what happens?

So, you got it working in Word? There are shortcut keys in Word that are equivalent to Format Painter. But I didn't seem to find the shortcut keys in Excel.

Maybe the following is the alternative if you can't get Format Painter working. I found the following from a reliable site and I tried it. It worked.

**********************************************************************************************

Sub PasteFormats()
Selection.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub


You can put this code into a standard macro module, then assign a keyboard shortcut to it. Then to "paint a format" do a copy (Ctrl-C) on the cell or range you want to copy formats from, then select the cell or range you want to copy the formats to, then type the keyboard shortcut you assigned to the PasteFormats sub.

***********************************************************************************************

Epinn

I followed your advices, position the cursor with the paint brush in D1,
click and... nothing happens (only the brush disappears)!
I have version 2002 SP2
???
Horst

 

[Epinn]

position the cursor with the paint brush in D1,
click and... nothing happens

After you clicked the format painter, did you see the border of the selected range (A1:A10) "move/revolve?

Epinn

I followed your advices, position the cursor with the paint brush in D1,
click and... nothing happens (only the brush disappears)!
I have version 2002 SP2
???
Horst

 

[Epinn]

When you assign the shortcut keys please make sure you don't overwrite the existing ones.

Epinn

Based on the following, Format Painter in Excel 2002 should work the same.

"The Format Painter in Excel 2002
Excel lets you use the Format Painter as well. Click the cell containing the formatting you want to use, click the Format Painter icon, and click one cell or drag a range of cells to duplicate the formatting. Cell formatting, in this case, includes character attributes, number formats, alignment, borders, patterns, and protection."

Do you want to try to add another Format Painter icon to the toolbar and see what happens?

Click Tools > Customize > Commands > Format, then scroll down on the RIGHT side until you see Format Painter. Click and drag it to the toolbar.

Now try to use this newly added format painter and see what happens?

So, you got it working in Word? There are shortcut keys in Word that are equivalent to Format Painter. But I didn't seem to find the shortcut keys in Excel.

Maybe the following is the alternative if you can't get Format Painter working. I found the following from a reliable site and I tried it. It worked.

**********************************************************************************************

Sub PasteFormats()
Selection.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub


You can put this code into a standard macro module, then assign a keyboard shortcut to it. Then to "paint a format" do a copy (Ctrl-C) on the cell or range you want to copy formats from, then select the cell or range you want to copy the formats to, then type the keyboard shortcut you assigned to the PasteFormats sub.

***********************************************************************************************

Epinn

I followed your advices, position the cursor with the paint brush in D1,
click and... nothing happens (only the brush disappears)!
I have version 2002 SP2
???
Horst

 

[horst]

I am afraid I must have some problem in my Excel.
Actually in Paste Special I see only "Paste as text" and "Paste as
Unicode Text".
No paste all, formulas, values, formats, comments etc. I remember I once
had available.
When I test your macro I get an error when attempting to execute the
PasteSpecial line.
Do you know if there is some switch which may block the Paste Special
function?
Horst

 

[Gord Dibben]

Are you pasting from one workbook to another?

Do you have each workbook open in its own session of Excel?

If so, close one session and open both books in one instance of Excel.

The paste special should be regular.


Gord Dibben MS Excel MVP

 

[Epinn]

Gord,

Thank you for coming to our two-people party.

We started with copy/paste but Horst didn't see all the options when clicking the paste options icon. So, we tried Format Painter. He also seemed to have a problem.

Then I suggested a little test. Select A1:A10, fill it with color. Then use the Format Painter to paste the color to D110. He seemed to have a problem as well. All this in *one* worksheet.

He also had a problem with the macro (assigned to shortcut keys) which does the job of the Format Painter.

I run out of ideas. Also, I don't use V 2002. I'll hand it over to you and I'll stay tuned.

By the way, Gord, do you know of any shortcut keys equivalent to the Format Painter? You know, in Word we have Ctrl+Shift+C and Ctrl+Shift+V. I came up blank with Excel.

Epinn

"Gord Dibben" <gorddibbATshawDOTca> wrote in message Are you pasting from one workbook to another?

Do you have each workbook open in its own session of Excel?

If so, close one session and open both books in one instance of Excel.

The paste special should be regular.


Gord Dibben MS Excel MVP

 

[horst]

No, I am copying within the same sheet
Horst

Are you pasting from one workbook to another?

Do you have each workbook open in its own session of Excel?

If so, close one session and open both books in one instance of Excel.

The paste special should be regular.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Not according to your original post which read.......

The usual cause of not seeing the regular Paste Special dialog is that you are
pasting material copied from and external source.

I have no more ideas.


Gord