Copying Certain Sheets from Active WB and creating New WB

[honger]

Hello All,

I currently have macro that creates new WB in a designated drive.

I also have a combobox which decides which sheets to copy. So for example, if master is selected, it will copy sheeets 1,5,2 and if template1 is selected it will copy sheets 7,5,2. etc

THis is the current code i have but i am getting error on the Sheets(Array( portion of the code. Can someone help me out what I am doing wrong? i think the line with RED fonts are the problem as macro runs fine without that line and combobox3.

Dim vFilename As Variant
Dim vPath As Variant
vpath1 = "[URL="file://\\tarcds01\eCTD_Submission\TRACKERS\"]\\tarcds01\eCTD_Submission\TRACKERS\[/URL]" & MyProduct & "\" & MyApplication & "\"
vpath2 = MyProduct & "-" & MyApplication & "-" & MySCN & "-" & MyEvent

[COLOR=#ff0000]If ComboBox3.Text = "Master" Then Sheets(Array("Sheet1", "Sheet5", "Sheet2")).Copy
[/COLOR][COLOR=#000000]ActiveWorkbook.SaveCopyAs Filename:=vpath1 & vpath2 & ".xlsm"

[/COLOR]If ComboBox3.Text = "Template1" Then Sheets(Array("Sheet7", "Sheet5", "Sheet2")).Copy
ActiveWorkbook.SaveCopyAs Filename:=vpath1 & vpath2 & ".xlsm"

'Reference the new workbook under  a friendly name
    Set newWBK = Workbooks.Open(vpath1 & vpath2 & ".xlsm")
'Make the new workbook active
    newWBK.Activate

 

[alansidman]

What is the error message you are getting? And when you click on debug, does the line in RED appear as highlighted in your code?

 

[honger]

i am getting Run-time error 9. Subscript out of range, and when i debug, the below code gets highlighted. I am very new to vba, thank you for your patience

Sheets(Array("Sheet1", "Sheet5", "Sheet2")).Copy

 

[honger]

would it be easier to delete sheets while creating the new WB?

 

[alansidman]

Try defining each array and then using the variable as the object to copy.

For example

Dim Arr1 as Array

Arr1=Array("Sheet1","Sheet5",Sheet2")

other code

If combobox3.text = "Master" then Sheets(Arr1).Copy

more code

 

[honger]

Thank you for the quick reply, i am getting this error for "Array" while i am typing Dim Arr1 as Array

Compile error: Expected: New or Type name

 

[honger]

So i replaced Array with myArray and it took it, however now i am getting

compile error: extected: )

Arr1 = ("sheet1","sheet2","sheet5")

 

[honger]

actually myarray does not work, am getting syntex error..