How to save to a new worksheet.

[j_Southern]

The attached workbook attempts to illustrate a problem I have not been able to solve
There are 2 data worksheets TAPEBUDGETA and LABELBUDGETA and a UTILITIES sheet.
The problem I am having shows when the button marked FILE on UserForm1 is clicked. This button fires off code which has the following purpose.
It creates a new workbook
It takes a range from three worksheets (only two in the down load) in turn and copies it. It creates worksheet in the new work book and names it. It then pastes the copied data as values. For the second and third sheets it has to create a new worksheet.
I get an error every time at Set wsB =wbB.Worksheets(2).
I must have coded wrongly somewhere. I hope that someone can see where and put me right.
John

[Arkadi]

Are you still on Excel 2010?
In my version (2016), a new workbook only has one sheet by default, so your code is trying to set wsB as the second sheet in a book with only one worksheet.

Try

Please Login or Register  to view this content.

for sheets after the first one.

[j_Southern]

I am using Microsoft 365 now but the offending code was written several years ago on probably 2008. Thanks for your interest and suggestion. I will try it and let you know how I get on.
John

[Arkadi]

Hi John,

That would be the reason then... your newer version of Excel would start a book with only 1 sheet, while in 2010 you'd get 3 sheets in a new workbook.

Arkadi

[j_Southern]

Arkadi,

Your code worked and I am now producing new sheets at will!
Many thanks for your insight.
John

[Arkadi]

My pleasure John! Thanks for the feedback, the rep, and marking the thread as closed Happy coding

[Norie]

The important difference between Excel2008 and 365 as far as this problem is concerned is that 2008 starts a new workbook with 3 sheets but 365 starts a new workbook with1 sheet (I think)

As far as I know in all versions you can set the no of sheets to have in a new workbook via File>Options...

[j_Southern]

AliGW,
Yes I can now see this the same thread. However I was so pleased with Arkadi's help that I marked the thread as solved. Too early it turned out. I needed more help so I started a new thread as the previous one was closed.
I hope you can forgive me.
John
Norie,
In gathering the information you needed I realised that I was looking at a clue as to what might be going on.
I started the app from scratch and fired off the code above. I got an error message "subscript out of range" at the line With Sheets("TapeBudgetA").
I repeated this after I had remed out all the code that was involved with Sheet("TapeBudgetA") and got a "subscript out of range" at the line SheetName="TotalBudgetA!...……….With Sheets(SheetName)
It was then that I realised that I was looking at the code overlying a workbook called Book1 which had one sheet named "LabelBudgetA". I then decided to add the line "With Workbooks("NewBudgetv5.0.xlsm")" before the sheet names. When running this I got the same error message.
I am now sure that my code is muddling up which workbook is active but I don't know how to sort this out.
The important difference between Excel2008 and 365 as far as this problem is concerned is that 2008 starts a new workbook with 3 sheets but 365 starts a new workbook with1 sheet (I think)
John
P.S. I have been chastised for starting a new thread to cover the same subject. This posting was on the illegal thread, so I hope you see it here.
I have just seen Norries comment which may mean the old code could be used, but I would like to see a more general answer if possible