How to copy worksheets to a new workbook

[Osprey2]

I am trying to develop code top copy 3 worksheets to a new workbook,
I have got this far but I am now stuck and could use some help.

Please Login or Register  to view this content.

This version stops at the line marked error with a "Subscript out of range" message which usually means a name doesn't exist.
I think they are there.
John

[Osprey2]

I forgot to add attachment

[Marc L]

As an unique codeline is necessary to copy 3 worksheets to a new workbook like this VBA demonstration :

Sub Demo0()
    Sheets(Array("LabelBudgetA", "TapeBudgetA", "TotalBudgeta")).Copy
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[ByteMarks]

Does this work for you?

Please Login or Register  to view this content.

[Osprey2]

ByteMarks Thanks for your interest. I had not thought of using an array. Before I plug your code into the app could you confirm the meaning of the line

Please Login or Register  to view this content.

Its the word CUT which worries me as I want the originals to remain..
MarcL Thanks to you as well with the same basic idea as ByteManrks
John

[ByteMarks]

The cut moves the table to C2 of the sheet.
For example TotalBudgeta_Table starts at F5 originally and is shifted to C2.

[Sintek]

I think ByteMarks misunderstood your initial code...
As per Marc...this is all you need...substituting path and file name with yours...

Please Login or Register  to view this content.

[ByteMarks]

I think ByteMarks misunderstood your initial code...

Possibly. It was from this line that I got the idea it had to be in C2.

Please Login or Register  to view this content.

However, you still might want to remove the Return buttons as they won't work in an xlsx

[Sintek]

However, you still might want to remove the Return buttons as they won't work in an xlsx

See amended...
Not any fault of yours...OP was trying to copy the sheets data to a new sheet within a new workbook and also copying the sheet...this initially confused me too...

[Marc L]

As the initial post is unclear, the reason why we could not 'understand' so my 'restart' help …

[Osprey2]

I seem to have confused all of you and in different ways too! I am sorry.
What I want to achieve is this.
At the present we have a budgeting app. This has 26 sheets, and all the calculations needed to account for our future costs and sales. It has 4 userforms and is a bit unwealdy.
We have decided that we don't need to distribute this to all the people who have contributed to the app but we do need to tell them the results.
So I need to make a small workbook with 3 sheets one for each of our product type Labels and Tapes and one for the total. The main app will still hold those three tables which will still be useful for next year
I hope this makes a bit more sense. I need to form a new workbook and copy three worksheets to it.
John.

[ByteMarks]

sintek's Post #7 is probably the most complete solution, but include the lines you had originally to handle the DisplayAlerts when to saving to xlsx to avoid the "Macro free" warning.

[Sintek]

sintek's Post #7 is probably the most complete solution, but include the lines you had originally to handle the DisplayAlerts when to saving to xlsx to avoid the "Macro free" warning.

Don't need to...Newbook has no code...xlsx

[ByteMarks]

Sorry, you're quite right. Pity there's no face palm emoji.

[Sintek]

More than welcome to take mine...I often make use of it lol...

istockphoto-1316006857-612x612.png

[ByteMarks]

lol thanks.

[Marc L]

Such a big one ‼

[Sintek]

Such a big one ‼

You right...not such a big blunder lol...requires small one this time...updated...

[Marc L]

Yes now big enough …

That recalls me on another forum where I 'The Doctor' used a bid 'Dalek' « Explain ! » picture
in order to make OP understand an Excel forum is very not a mind readers one …

[Sintek]

Hey...If you can't have fun coding...Then why do it...
perhaps the forum will add more emoji's...more than enough coders to assist lol...

[Osprey2]

I think ByteMarks misunderstood your initial code...
As per Marc...this is all you need...substituting path and file name with yours...

Please Login or Register  to view this content.

I am self taught in Excel and vba, and although everyone seems to have accepted sinteks code which I have quoted above, I am struggling to understand how it works.

I cant see where the new workbook is added.
The line Sheets(Array..... Doesn't have a workbook.
The Activeworkbook in Line "With Activeworkbook" Seems to me to refer to the workbook with the code in it not the 3 sheets to be saved.
I think that perhaps you have overrated me and have missed some lines that you think I know should be included.
In any case it fails at the first lne.
If sintek could explain for me it would be a great help.
John

[Sintek]

I am self taught in Excel and vba

Mee too...With the help of this amazing forum and it's members...And loving it...


The only way it would fail on that line is if those 3 sheets do not exist...Code works for your sample supplied...

Please Login or Register  to view this content.

[Osprey2]

Thankyou Sintek for the explanation of your code. I had not realised the depth of the Array line. As well as that I had not realised that three sheets had gone missing. Goodness knows the Budget sheets had been deleted but they were.
Your code works fine now.
Thanks too to the others who commented and helped.
#John

[Sintek]

Glad to have contributed...Tx for rep +