Copy existing (hidden) worksheet and replace/overwrite existing worksheet in same workbook

Hi, thanks for looking.

I am trying to create a macro that will copy an existing hidden worksheet named "Quality Report (2)" and replace an existing (Active) worksheet called "Quality report".

I have searched and tried a few methods but they all just seem to paste it to a new workbook, or before/after a worksheet, I need to overwrite the active worksheet with an exact duplicate, formatting, formulas etc.

Seems like it should be straight forward but I'm struggling.

Any help is greatly appreciated.

Hi AlphaFrog

Thanks for replying. This is giving me 'There is not enough Memory to complete this action" error and then runtime error '1004' - PasteSpecial method of Range class failed' on the line

Sheets("Quality Report").Range("A1").PasteSpecial xlPasteAll

I have restarted and closed all other applications, running 64 bit with 16gb Ram so shouldn't struggle with this, any idea's?

Hi AlphaFrog

Thanks again but still not working. I still get the memory error and the runtime error 1004 - but this time it says

'Copy method of range class failed'

I'm thinking maybe as a clumsy workaround I could just copy 'Quality Report (2)' before the 'Quality Report' and delete the original, may be worth a go as overwriting the original seems a bit problematic.

Unless you have any better ideas or know why the above is failing?

Thanks AlphaFrog, that worked perfectly.

I wasn't suggesting you do that, but you certainly saved me a lot of time and effort and it's very much appreciated.

Stuart