VBA to create new Workbook and copy worksheet into it dynamically

Hi, I am creating a VBA to create a new workbook and copy a worksheet into it. I have the following code I got off youtube, which works for the first time, but when I repeat it gets an error because the code says "book1" and new book is Book2 etc

Please Login or Register  to view this content.

I would also like to name the the new workbook from a combination of set term and a variable, the code I have for this is the following:

Please Login or Register  to view this content.

I would like to do this all together if possible, create new workbook by the above naming and then copy a worksheet into it.

cheers Rob

use an object when adding the workbook.

something like

dim wb as excel.workbook
set wb =workbooks.add


then something like

Sheets("REPORT").Copy BEFORE:=wb.Sheets(1)

i havent tested this, but should help

Rob

You can copy a sheet like this to create a new workbook and you can refer to the workbook the code is in using ThisWorkbook.

Please Login or Register  to view this content.

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.