Copying 3 given ranges to 3 sheets in a new workbook fails at second copy

[j_Southern]

I have a workbook which has, amongst many others, 3 sheets which each have a named range I want to copy and paste into another, new, workbook.
The code I am trying to use was written several years ago and has worked without problems till now.

Please Login or Register  to view this content.

It fails at the second use of the do loop at the line marked ******* with a message "Subscript out of range" Perhaps there is no sheet2 ?
I think that new workbooks used to open with 3 sheets when the code was written but perhaps with only 1 sheet now?
I have not manged to solve this problem and would like some help please.

I can confirm that there are three sheets with the names in the DO loop: and that these 3 all have a named range "Budget"
On the first loop the new work book has been created and the Range "LabelBudgetA".Budget has been copied and pasted.
The failure happens on the second loop.
Thanks in anticipation
John

[mehmetcik]

Hi John

The Macro is looking for three sheets named: LabelBudgetA, TapeBudgetA and TotalBudgetA

Please Login or Register  to view this content.

It uses N as a Counter.

Please Login or Register  to view this content.

Change this to the number of sheets plus 1 if you want to amend this.

However the macro is over complicated.

So I will simplify it so you can maintain it going forward.

Please Login or Register  to view this content.

[davesexcel]

You can set the number of worksheets to add to the new workbook

Please Login or Register  to view this content.

You probably need to set it back to 1 at the end or you will always have 3 sheets for new workbooks.

[Sintek]

Another Option...

Your code does not show Save File Name...

Please Login or Register  to view this content.

[j_Southern]

Many thanks for 3 interesting and different answers to my problem. I will try them all out and see which fits best.
I must say that the way that this forum, or its contributors, work is a wonderful exception to much of modern life.
I really am most grateful to you all.
John

[Sintek]

.........................
THANKS.gif