Delete sheets and create a new workbook in Shared Workbook.

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

So if i were to leave the source WB as unshared, and then share it when new WB is created, do i need to just turn on sharing when the newWBK is saved? i put the following code in but getting error again
new code

newWBK.SaveAs newWBK.FullName, xlaccess:=xlShared

replaced

newWBK.Save
newWBK.Activate

Compile error: Named argument not found

i think im getting close. It is doing everything i ask for with the below code, however since the WB is already created it asks me to copy over the exisiting file. Is there anyway to just save without the prompt? i inserted the following code, but its asking to copy over the exisitng file.

ActiveWorkbook.Save ActiveWorkbook.FullName, accessmode:=xlShared

Application.DisplayAlerts = False

stupid oversight on my part. ive solved it

Application.DisplayAlerts = False
ActiveWorkbook.Save ActiveWorkbook.FullName, accessmode:=xlShared
Application.DisplayAlerts = True