deleting worksheets from a file which has macros in it........does not work.

**welchs101** · 05-16-2012, 02:04 PM

Hi,

I have this macro and i save a copy of this file (along with all its worksheets) to a specified location.

I do this so that i can then reopen the file and use all of the VBA code that is located in the "ThisWorkBook" module and some of the VBA code in one of the worksheet modules.

So i open this copied file......i begin to delete the worksheets i dont need........and the macro will delete the first worksheet but then it will just STOP.

NOTE: iF I run the macro such that you have the VBA code open then when i open the copied file i get a question about "enabling" the macros. When ever i get asked this question.....the macro will run as its supposed to ......

I am enclosing the macro. When you click launch what is supposed to happen is that all of the sheets in the workbook except the worksheet named "Prompt" and the worksheet named "Four col sht" are to be deteled.........but this does not happen.

It will only happen provided you have the VBA code open and you get a question asking about enabling / disabling the macro for the "copied" file.

Any ideas?

**welchs101** · 05-16-2012, 03:38 PM

anyone have any ideas?

Also, if someone can just run it and verify that they get the same result i would appreciate it.

note: if you do run it you need to change the "path" to something that is valid on your machine.

i just dont get this at all......after i open the file and then delete teh first sheet in the copied workbook the macro just stops.

very very strange.

**welchs101** · 05-16-2012, 06:40 PM

just curious why no has responded on this.......did i post it incorrectly or did i confuse everyone?

i am really lost on this one........at my limit actually...........could really use some help

**Leith Ross** · 05-16-2012, 10:13 PM

Hello Welchs101,

I wrote a macro to make a copy of the workbook with the sheets and code you want. The new workbook stays open after it is created. You can easily write a macro to save this where you want. Copy this code into into a new VBA module in your workbook.


' Written: May 16, 2012
' Author:  Leith Ross
' Summary: Creates a copy of the workbook with only selected worksheets.
'          VBA code from various modules and UserForms is copied as well.

Option Explicit

Sub CopyWorkbook()

    Dim Code As String
    Dim FormName As Variant
    Dim FormNames As Variant
    Dim i As Long
    Dim NewVBProj As Object
    Dim NewWkb As Workbook
    Dim NewStdMod As Object
    Dim Wks As Worksheet

        Set NewWkb = Workbooks.Add
        
        ThisWorkbook.Worksheets("Prompt").Visible = xlSheetHidden
        
        ThisWorkbook.Worksheets("Prompt").Copy After:=NewWkb.Worksheets(NewWkb.Worksheets.count)
        ThisWorkbook.Worksheets("Four_Column_Defn").Copy After:=NewWkb.Worksheets(NewWkb.Worksheets.count)
                
        Application.DisplayAlerts = False
            For i = 3 To 1 Step -1
                NewWkb.Worksheets(i).Delete
            Next i
        Application.DisplayAlerts = True
        
            Set NewVBProj = NewWkb.Application.VBE.ActiveVBProject
        
                Set NewStdMod = NewVBProj.VBComponents.Add(ComponentType:=1)
                
                With ThisWorkbook.VBProject
                
                    With .VBComponents("ThisWorkbook").CodeModule
                        Code = .Lines(1, .CountOfLines)
                        NewVBProj.VBComponents("ThisWorkbook").CodeModule.AddFromString Code
                    End With
                    
                    With .VBComponents("Module2").CodeModule
                        Code = .Lines(1, .CountOfLines)
                        NewStdMod.CodeModule.AddFromString Code
                    End With
                    
                    FormNames = Array("DateFormB", "PDM", "UserForm1")
                    
                    For i = 0 To UBound(FormNames)
                        FormName = FormNames(i)
                        FormNames(i) = Environ("Temp") & "\" & FormName
                        
                        .VBComponents(FormName).Export FormNames(i)
                        NewVBProj.VBComponents.Import FormNames(i)
                        
                        Kill FormNames(i)
                        Kill FormNames(i) & ".frx"
                    Next i
                    
                End With
            
End Sub

**welchs101** · 05-16-2012, 09:42 PM

do you think that because the orignal file has a macro module and i am making a copy and then opening the copy which would also now have a macro module ........i assume on opening via vba that the macros are disabled in this new file......but would this cause a problem.

should i change the title of this thread?

can someone help?

**welchs101** · 05-17-2012, 06:25 AM

thanks Leith. I had all but given up on this. Thanks a lot.

I see some very interesting things in the code......i will have to review them..........opportunity to learn. I think i know whats goign on.

thanks again!!!!!!!!!!!!!!!!!!

**welchs101** · 05-17-2012, 06:29 AM

oh, just wanted to add it works great.

To get it to work i did have to change a setting in the "trust center". I had to place a tick mark beside
"Trust access to the VBA project object model".

Once i did that it worked fine. If i did not do that then i got a run time error.

"Programmtic access to the visual basic project is not trusted."

**welchs101** · 05-17-2012, 06:34 AM

For those interested in what this is i looked it up:

Trust access to the VBA project object model:
This setting is for developers and is used to deliberately lock out or allow programmatic access to the VBA object model from any Automation client. In other words, it provides a security option for code that is written to automate an Office program and programmatically manipulate the Microsoft Visual Basic for Applications (VBA) environment and object model. This is a per user and per application setting, and denies access by default. This security option makes it more difficult for unauthorized programs to build "self-replicating" code that can harm end-user systems. For any Automation client to be able to access the VBA object model programmatically, the user running the code must explicitly grant access. To turn on access, select the check box.

FYI: So if someone else wants to run my macro they will have to have this turned on.

**welchs101** · 05-17-2012, 06:57 AM

Leith,

Your code is very well written and easy to understand..well for the most part. there are few things i need to look up but i get what you're doing even though i may not understand some of the commands.....nicely written.

**welchs101** · 05-17-2012, 10:39 AM

hi all, i did want to let you know i found a slight bug in the code.

If i ran the code for the first time i would get an error that could only be caused by transferring vba code from "thisworkbook" section to a workbook that should not have this information transferred to it.

So i realized that i needed to be more specific in "exactly" specifying what workbooks are doing what. See code below.......note the code which does not specify the exact workbook references is commented out........

''copy thisworkbook code over to new workbook
'Set NewVBProj = NewWkb.Application.VBE.ActiveVBProject
'
'With wrkbk_macro.VBProject
'
'    With .VBComponents("ThisWorkbook").CodeModule
'        Code = .Lines(1, .CountOfLines)
'        NewVBProj.VBComponents("ThisWorkbook").CodeModule.AddFromString Code
'    End With
'
'End With



Dim sourceBook As Workbook
Dim destinationBook As Workbook
Dim codeFromSource As String

Set sourceBook = Workbooks(fl_macro)
Set destinationBook = NewWkb

With sourceBook.VBProject.VBComponents("ThisWorkbook").CodeModule
    codeFromSource = .Lines(1, .CountOfLines)
End With

With destinationBook.VBProject.VBComponents("ThisWorkbook").CodeModule
    .DeleteLines 1, .CountOfLines
    .AddFromString codeFromSource
End With

**Leith Ross** · 05-17-2012, 12:56 PM

Hello Welchs101,

Sorry about not mentioning making the change to the Trust Center settings. I assumed with your experience you would not need to be reminded. That was a bad call on my part because it deals with the VBIDE (Visual Basic Integrated Development Environment) and not just VBA macros.

Despite that, you managed to figure it out with no problem. Excellent work! Not many people would have figured that out on their own. From your comments, it looks like I did my job correctly. You were able to read the code, identify its functional parts, and modify it as needed.

I am going to mark this post as solved. If have any questions about the code, I will happy to answer them.

deleting worksheets from a file which has macros in it........does not work.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Re: deleting worksheets from a file which has macros in it........does not work.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions