Reputation: 3
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
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