Keegan Rivera
Keegan Rivera

Reputation: 3

Copying data to new workbook VBA

I need this to copy all of the data from one spreadsheet, create a new workbook, name it, and paste the data. I seem to have a bug in my code that opens two new workbooks, pastes my data in one and pastes the last thing I copied into the other and that is the file that it assigns the name to.

' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy


' Create new Workbook
Set NewBook = Workbooks.Add

' Name it and paste data
ActiveSheet.Paste
ActiveSheet.SaveAs Filename:="test.xlsx"
NewBook.Close

Upvotes: 0

Views: 3787

Answers (1)

nbayly
nbayly

Reputation: 2167

Your active sheet is not necessarily NewBook. You have to activate the workbook first as per:

' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy


' Create new Workbook
Set NewBook = Workbooks.Add

' Name it and paste data
NewBook.Activate
ActiveSheet.Paste
NewBook.SaveAs Filename:="test.xlsx"
NewBook.Close

Upvotes: 1

Related Questions