Not sure how to replicate cell formats along with cell values

I use the following code to replicate the values of cells in a range in one workbook to the corresponding cells in another workbook:

Please Login or Register  to view this content.

Fairly straightforward and works just fine in replicating the cell values.

However I also need to replicate the cell colour and font colour attributes for each cell in the range. It wouldn't bother me if it replicated all format attributes, but those are the two that matter most.

Is there a similarly straightforward means to replicate the individual cell's format attributes for the range?

Thanks.
Unfortunately that just wipes out all the content in the range in the original sheet (but leaves the formatting) and doesn't copy anything across to the other sheet (values or formats).

Ok, forgive me but my original code operates fine, with cells being copied from one workbook to a different workbook. In my code, ActiveWorkbook and ThisWorkbook do not seem to refer to the same workbook.

For context, the code preceding this activates the first workbook (the one I want to copy from, in this case the workbook called "TPS~v.1.5.4") and then adds a new workbook, the one I want to copy to.

Please Login or Register  to view this content.

After all the copying is done, the code moves onto to save the new workbook, and give user option to rename from a default and choose a directory.

For some reason the code I use replicates the data from one to the other just fine. I don't however know why.

Originally Posted by JapanDave

Try this,

Please Login or Register  to view this content.

Cool, thanks, that works.

It copies values and font formats, but it isn't copying cell colours though.

I think the problem might be due to the colours in the original worksheet being created by conditional formatting.

Which might mean I'll need a different approach to the problem. I have a couple of ideas to try...