Consolidate Worksheets - Help pasting values

Dear experts,

I am having trouble tweaking the code below. I need to paste values and formats to a new workbook (so the one i'm copying from stays intact).

Please Login or Register  to view this content.

Ty

Try change to

Please Login or Register  to view this content.

Hi Jindon!
Thank you for replying. I tested it, but i got an error due to merged cells.
Also, to be safe, instead of pasting to a new sheet how do paste it to a new workbook?
Ty.

Then how about

Please Login or Register  to view this content.

Originally Posted by jindon

Then how about

Please Login or Register  to view this content.

I get an error 6 'overflow' problem, highlighting .usedrange line.

What happen if move that line to outside loop.
i.e

Please Login or Register  to view this content.

If you still have problem, you need to upload a workbook.