VBA to copy Worksheet to new Workbook

I am using VBA to cycle through an array, and save each workbook with a new file name. What I am wanting to do is while I have the workbooks open, copy a worksheet from the open workbook to a new workbook. So for example, let's say my Array("Monday", "Tuesday", "Wednesday") and each workbook always contains two worksheets New and Old. When I have Monday open, I want to copy the worksheet "New" to a new workbook. I would need to keep that new workbook open tho, because I want to copy the same worksheet from Tuesday, and Wednesday to the "new" workbook as well. How would VBA accomplish this?

To copy a sheet to a brand new workbook requires simply

Please Login or Register  to view this content.

you can then use

Please Login or Register  to view this content.

to get a reference to the newly created workbook. Thereafter

Please Login or Register  to view this content.

as an example.

Great tips from OnErrorGoto0, one small tweak I would suggest:

Please Login or Register  to view this content.

@JB
Depends where you want the sheet!

@jo15765

The first line of code creates a new workbook containing just the copied sheet. If you do not specify a destination, you will always get a new workbook.

sorry!..my mistake!

Based on the OPs indication he is cycling through sheets Monday-Wednesday.... putting them into the new workbook in the same order would seem prudent, thus my suggestion. Without it, the sheets would end up in the new workbook Mon-Wed-Tues. That may not matter.


It's also worth noting that if the sheets being copied all have the same name "NEW" then you will have trouble copying them into a new workbook without renaming something.

I take your point regarding the order. Either that or use before:=wb.Sheets(1) if you want the latest sheet first - I presumed that the names were fictitious.

Workbook name refers to the workbook you are copying from, not to. wb is the destination workbook.

Yes, you should include the file extension (or store a reference to the workbook in a variable if your code opens it as well).

I cannot tell you how to best adapt your code since you have not posted any, but yes you can use two workbook variables and simply copy the sheets from the first three books to one and the second two books to another.

On a slight tangent, I note that you post your questions in several different forums (without cross-posting, happily) - is there a reason for this? It occurs to me that if you have several questions on parts of the same project, it might make life simpler if a few people on one or two forums became familiar with the general theme. (Though obviously there are no guarantees that this would happen!)

Apologies, I think you misunderstood my point. As far as I can see, you do not cross-post - which is posting the same question on several forums. You post different questions on different forums - I was simply intrigued as to the reason (though it is none of my business really so please do not feel obligated to answer!)