how ot keep value and drop formula while copying

[Guest]

Hi all,

I use " range.copy destination:= " to do copying between two worksheets in
two workbooks. I want to copy value instead of formula to my new sheet, how
can I do it?

Clara

 

[Ronald Dodge]

This will be a 2 liner code:

SourceRange.Copy
DestinationRange.PasteSpecial(xlPasteValues)

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Guest]

Hi Ronald,

thank you very much! while copying value, I do not want to lose format(such
as background color, font size) How can I combine the xlPasteFormat with
xlPasteValue
?

Clara

thank you so much for your help

 

[Ronald Dodge]

Change the xlPasteValues to xlPasteValuesAndNumberFormats

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

If dealing with other items besides numbers, you can also use the following:

DestinationRange.PasteSpecial(xlPasteValues)
DestinationRange.PasteSpecial(xlPasteFormats)

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Guest]

Hi Ronald Dodge,

Thank you very much! I have used your second method and it works very well.
As for your first method, where could you find the xlPasteValuesandFormat
argument?
There is still another question to be answered; there is icon image on the
left top of this sheet, and during copy process, it lost. How can I copy the
image to my new sheet?

Best Regards,

Clara

 

[Ronald Dodge]

Sorry for the delayed response as yesterday, we had technical issues outside
of any one's controls, that prevented me from working. I had to get caught
up today to make up for that lost time. Not going to have much done when
you have a downed power line catching the grass on fire and then having
transformers blown out by power surges after the power has been rerouted.

You should be able to locate this in your help files, but then that would
only be if you have VBA Help installed on the system, which under the
default installation options, this is not included.

If VBA Help isn't installed, go through the installation process, and it is
under "Office Shared Features>Visual Basic for Applications>Visual Basic
Help"

Once you have that, you can then either lookup "PasteSpecial" in the index,
or you can drive down the content categories under "Content" with "Microsoft
Excel Help>Programming Information>Microsoft Excel Object
Model>Methods>P>PasteSpecial Method" For the method that I was giving you,
it's assumed to be in the same application, so it's using the Range object,
not the Worksheet object. Where you see the "Paste" argument, click on the
"XlPasteType" bold text for it to list a set of constants you can use for
that particular argument. The default is xlPasteAll.

Now for your second question, it would be via the worksheet object, not the
range object, which case is a bit trickier. Generally, I don't like using
either the Select or Activate methods, but this might be one of those
exceptions to the general rule so as to be able to paste it in a proper
place. That is cause you have to use the worksheet object, not the range
object to apply the PasteSpecial method.
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000