Results 1 to 9 of 9

VBA - Delete sheets in activeworkbook

Threaded View

  1. #1
    Registered User
    Join Date
    12-07-2013
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    91

    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
    Last edited by honger; 05-07-2014 at 10:37 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] ActiveWorkbook.Close closing sheets, but not the window
    By mintymike in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-28-2014, 07:36 AM
  2. Different between ActiveWorkbook.Sheet and ActiveWorkbook.WorkSheets
    By Ranjani in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2009, 12:56 AM
  3. Macro to delete sheets and saves remaining file does not properly delete module
    By pherrero in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2005, 08:12 PM
  4. Replies: 0
    Last Post: 06-21-2005, 01:05 PM
  5. Replies: 2
    Last Post: 05-07-2005, 12:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1