VBA - Delete sheets in activeworkbook

Hello All, i have also posted this at MrExcel because i kind of need a solution fairly quick and i hoped to better my chance by posting at 2 sites.

http://www.mrexcel.com/forum/excel-q...ml#post3801354
I am working on a macro which creates a new workbook by copying activeworkbook and while doing so delete some sheets.

For some reason, i can see that it deletes it, but then it creates it again. In question is highlighted in red font. Can someone point to me what i am doing wrong?

 some Codes to create new workbook then comes below
'name new WB
    Set newWBK = Workbooks.Open(vpath1 & vpath2 & ".xlsm")
'Make the new workbook active
    newWBK.Activate
    
Application.DisplayAlerts = False
If ComboBox3.Value = "Master" Then Sheet7.Delete
Application.DisplayAlerts = True

ActiveSheet.Range("Q1").Value = MyProduct
ActiveSheet.Range("R1").Value = MyApplication
ActiveSheet.Range("S1").Value = MySCN
ActiveSheet.Range("T1").Value = MyEvent


FriendlyName1 = "Link to Documents with SCN"
MyPath1 = "https://tarregsp01p/sites/Regulatory/NextDocs%20Document%20Library/Forms/Detailed%20View.aspx?View={0C67A7C1-5BDA-4B11-A42D-4064D61BF7EC}&FilterField1=SCNNumber&FilterValue1=" & MySCN
FriendlyName1 = "Link to RegApp with product"
MyPath2 = "https://tarregsp01p/sites/Regulatory/_layouts/FormServer.aspx?XmlLocation=/sites/Regulatory/RegApp/Submission/Submission-" & MyProduct & "-" & MySCN & ".xml&DefaultItemOpen=1"


ActiveSheet.Range("A1").Formula = "=Hyperlink(""" & MyPath1 & """, """ & "Link to Documents with - " & MySCN & """)"
ActiveSheet.Range("C1").Formula = "=Hyperlink(""" & MyPath2 & """, """ & "Link to RegApp for - " & MySCN & " " & MyEvent & """)"

With Range("a1").Font
    .Bold = True
End With

With Range("c1").Font
    .Bold = True
End With
    
newWBK.Save
newWBK.Activate

'Close the template without saving
ThisWorkbook.Close False


End Sub

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

http://www.mrexcel.com/forum/excel-q...eworkbook.html

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Below is the entire code i have.

It is supposed to copy the open active workbook and saveas in a different location. While doing so i want some sheets to be deleted according to value chosen in combobox3. Everything works but it wont delete that sheet while creating the new workbook. It does delete it, but then it gets created again . I think i know this is happening because when i got rid of the Application.Displayalerts, diaglog box asked to confirm the delete and it deleted the sheet for a second but it recreated it

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

    
MsgProceed = MsgBox("File Name : " & MyProduct & "-" & MyApplication & "-" & MySCN & "-" & MyEvent, vbYesNo, "Create New Submission?")
If MsgProceed = vbNo Then Exit Sub

Unload UserForm1
UserForm1.Hide

Dim vFilename As Variant
Dim vPath 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
    
Application.DisplayAlerts = False
If ComboBox3.Value = "Master" Then
Sheet7.Delete
End If
Application.DisplayAlerts = True

ActiveSheet.Range("Q1").Value = MyProduct
ActiveSheet.Range("R1").Value = MyApplication
ActiveSheet.Range("S1").Value = MySCN
ActiveSheet.Range("T1").Value = MyEvent


FriendlyName1 = "Link to Documents with SCN"
MyPath1 = "https://tarregsp01p/sites/Regulatory/NextDocs%20Document%20Library/Forms/Detailed%20View.aspx?View={0C67A7C1-5BDA-4B11-A42D-4064D61BF7EC}&FilterField1=SCNNumber&FilterValue1=" & MySCN
FriendlyName1 = "Link to RegApp with product"
MyPath2 = "https://tarregsp01p/sites/Regulatory/_layouts/FormServer.aspx?XmlLocation=/sites/Regulatory/RegApp/Submission/Submission-" & MyProduct & "-" & MySCN & ".xml&DefaultItemOpen=1"


ActiveSheet.Range("A1").Formula = "=Hyperlink(""" & MyPath1 & """, """ & "Link to Documents with - " & MySCN & """)"
ActiveSheet.Range("C1").Formula = "=Hyperlink(""" & MyPath2 & """, """ & "Link to RegApp for - " & MySCN & " " & MyEvent & """)"

   
newWBK.Save
newWBK.Activate

'Close the template without saving
ThisWorkbook.Close False


End Sub

Originally Posted by honger

I am working on a macro which creates a new workbook by copying activeworkbook and while doing so delete some sheets.

For some reason, i can see that it deletes it, but then it creates it again. In question is highlighted in red font. Can someone point to me what i am doing wrong?

I don't see any code where you copy the active workbook.

You open a workbook file and delete Sheet7 in that opened workbook,do other formatting, then save it.

You then close the workbook that contains the code (not the opened workbook).


I'm not sure what you want done?

Honger: Yes, it is now in compliance, but please read the link I provided on why it may backfire on you when you post to multiple sites.

You cannot use a worksheet's Code name (Sheet7) to refer to a sheet in another workbook. Sheet code names always refer to the same workbook that contains the code. You can only reference sheets in another workbook by Tab-name or index number.

This gets the Tab-name of Sheet7 from the original workbook and uses that tab-name to delete the worksheet in the copy.

    Application.DisplayAlerts = False
    If ComboBox3.Value = "Master" Then
        newWBK.Sheets(Sheet7.Name).Delete
    End If
    Application.DisplayAlerts = True