Copy certain sheets to a new workbook and maintain reference formulas

Hi there!

I'm having an issue with saving selected sheets to a new workbook.
In my case I want to save the sheets "AAA", "ABB", "ACA" to three different workbooks. But in every workbook, I want to also copy the sheet "Total" and "Total 2". When these sheets are included, I want to adapt the formulas to the new workbook and avoid external links.
My guess was to create a list in my workbook of all the sheets and select these sheets with a loop:

Please Login or Register  to view this content.

But when I run the macro, an error occurs. Could you please help me?

Many thanks!!

Okela

Hi maras!
Thanks it's working. However, I have an issue regarding the function within the worksheet "Total". Let's take the new workbook "ACA.xlsx" for example. The formula is:
=COUNTA([CopySheetsExample.xlsm]AAA!E5:E55)

I wish I could replace it accordingly with the correct workbook. Do you think it is feasible?

Thanks!

Okela

Thanks Maras! Ended up doing it differently.
Thanks again