Hi All,

I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.

The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete? 

Private Sub CommandButton1_Click()
 
Dim newWBK As Workbook

'Grab Data From UserFrom
MyProduct = UserForm1.ComboBox1.Value
MyApplication = UserForm1.ComboBox2.Value
MySCN = UserForm1.TextBox1.Value
MyEvent = UserForm1.TextBox2.Value
MyTemplate = UserForm1.ComboBox3.Value
 
Unload UserForm1
UserForm1.Hide

Dim vFilename As Variant
Dim vpath1 As Variant
Dim vpath2 As Variant
vpath1 = "\\tarcds01\eCTD_Submission\TRACKERS\" & MyProduct & "\" & MyApplication & "\"
vpath2 = MyProduct & "-" & MyApplication & "-" & MySCN & "-" & MyEvent

ActiveWorkbook.SaveCopyAs Filename:=vpath1 & vpath2 & ".xlsm"

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

'Select Template
Application.DisplayAlerts = False
If ComboBox3.Value = "Master" Then
    newWBK.Sheets(Sheet5.Name).Delete
    newWBK.Sheets(Sheet6.Name).Delete
    newWBK.Sheets(Sheet7.Name).Delete
    newWBK.Sheets(Sheet8.Name).Delete
    newWBK.Sheets(Sheet9.Name).Delete
    End If
Application.DisplayAlerts = True

Application.DisplayAlerts = False
If ComboBox3.Value = "BLA Annual Report" Then
    newWBK.Sheets(Sheet1.Name).Delete
    newWBK.Sheets(Sheet6.Name).Delete
    newWBK.Sheets(Sheet7.Name).Delete
    newWBK.Sheets(Sheet8.Name).Delete
    End If
Application.DisplayAlerts = True

'etc
'etc
'Note - there are multiple selections to choose from ComboBox3 and deletes sheets according to it from this WB

newWBK.Save
newWBK.Activate

'Close the template without saving
ThisWorkbook.Close False


End Sub