CODEWITHSUNDEEP
excelvba
Zfunk
Zfunk

Reputation: 1193

Creating new Excel workbook and copying specific tabs into it

I am trying to create a new workbook, and then copy specific tabs over into it, however getting an 'Invalid Qualifier' error on compiling.

I don't know what the current workbook will be called, hence the need for code to define this

Sub NewWb()

Dim Aname As String
Dim Bname As String
Dim FileUse As String
Dim wbkCurrent As Workbook

Set wbkCurrent = ActiveWorkbook

Aname = "NewWorkbook_"
Bname = Format(Now, "YYYY-MM-DD")
FileUse = Aname & Bname

Debug.Print FileUse

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=FileUse

wbkCurrent.Activate

wbkCurrent.Name.Worksheets(Array("Tab1", "Tab2", "Tab3")).Copy Before:=FileUse.Worksheets(FileUse.Worksheets.Count)

End Sub

Upvotes: 0

Views: 28

Answers (1)

SJR
SJR

Reputation: 23081

Try assigning a variable to the new workbook when you add it, and then you can reference it later in your code and avoid the need to activate things.

Sub NewWb()

Dim Aname As String
Dim Bname As String
Dim FileUse As String
Dim wbkCurrent As Workbook
Dim wbNew As Workbook

Set wbkCurrent = ActiveWorkbook

Aname = "NewWorkbook_"
Bname = Format(Now, "YYYY-MM-DD")
FileUse = Aname & Bname

Debug.Print FileUse

Set wbNew = Workbooks.Add
wbNew.SaveAs Filename:=FileUse

wbkCurrent.Worksheets(Array("Tab1", "Tab2", "Tab3")).Copy Before:=wbNew.Worksheets(wbNew.Worksheets.Count)

End Sub

Upvotes: 2

Related Questions