Modify code to transfer cell value and formats

[nickytraps]

Hi All,

I am using the below to transfer data between 2 workbooks. Is there a way (perhaps by modifying the .Value) to also transfer the source formatting as well? Any assistance would be greatly appreciated. Thank you.

Please Login or Register  to view this content.

[jolivanes]

Just a regular Copy/Paste should do that

Please Login or Register  to view this content.

or any variation of the above

[Fluff13]

Maybe

Please Login or Register  to view this content.

[nickytraps]

Thank you both.

jolivanes - how do i modify yours to work with the src and dest

Fluff13 - I am getting a 424 error on the pasteformats line

[Fluff13]

What is the error message?

[nickytraps]

Run-time error '424' Object required

[Fluff13]

In that case check that dest has been declared & set properly.

[jolivanes]

Re: how do i modify yours to work with the src and dest
Show the whole code please

[nickytraps]

Fluff13 - It looks like dest has been declared and set - see below.

Please Login or Register  to view this content.

jolivanes - does my intial/original post provide the necessary info?

[Fluff13]

That looks fine, in which case I cannot see why you are getting an "Object required" error.
Can you post your entire code?

[nickytraps]

Please Login or Register  to view this content.

[Fluff13]

Ok, this

Please Login or Register  to view this content.

should be on two lines as per post#3

[nickytraps]

Got it. Ok that fixed the '424' error.

I am now getting a run-time error '1004' PasteSpecial method of Range class failed on the pastevalues line

[Fluff13]

If you step through the code using F8, once it has pasted the formats, does it go straight to the next line, or does it take you to some other code first?

[nickytraps]

When I step through that error pops up right after I pass the paste formats.

[nickytraps]

I think merged cells might be causing the problem. The source is a system generated report. At the top of the report (above the headers) there is a merged section that shows up after the paste formats.

[nickytraps]

I modified the system report and it was the merged section causing the error. Your code works great Fluff13. Thank you, I appreciate the help!

[Fluff13]

Glad you sorted it & thanks for the feedback