Deleting a Module

[LGMan]

Hi, just a quick question, I have a 'master' xls file with loads of vlookup and other things that the end user could easily mess up. So I have a procedure which amonst others, saves the file to a new name and copies - pastes values to convert evertying to text.

Can I also remove module 1 as part of the procedure.

Thanks

 

[AnotherHiggins]

Yes.

Something like this:

Sub DeleteModule()
    With Workbooks("Book1").VBProject
        .VBComponents.Remove .VBComponents("Module1")
    End With
End Sub

For this to work, you'll need to go into Macro Security settings and check "Trust access to the VBA project object model". Where you find that depends on your version of Excel. If you need help, just let us know what version you're running.



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[PHV]

You may also copy the sheets only instead of the entire workbook.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

And you may need a Reference to Microsoft VBA Extensibility. You can not do this in Word unless that reference is, ummm, referenced. Out of curiosity, is this required for Excel??

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[LGMan]

Thanks for the replies, It's for Excel, I have 2003 on one machine and 2007 on another, depends where I'm working!
I like the idea of copying the sheets, must admit I hadn't thought of this workaround.

 

[LGMan]

Is it possible to copy or move all the sheets at one, rather than copying/moving them one at a time?

Thanks

 

[fumei]

Loop through them.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[PHV]

A starting point (copy 3 sheets in a brand new workbook):

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LGMan]

Thanks PHV, your solution is something that I'll be able to use in the future.
I've found that for this instance...

Worksheets.Copy
ActiveWorkbook.SaveAs FileName:="my file" &Fname

This low tech approach works nicely as a co worker will be running the process on his machine when I'm out and about, so won't run into any Macro Security settings problems.