Removal of VBA Modules after copying from a template

As a follow on question from this question:

Martin Liss succesfully provided code adapted from Ron De Bruin to enable me to import a VBA Module into several files, in doing so getting rid of all existing modules in that file.

I have successfully used this code to implement a couple of modifications of the Function and some subsequent codes which were needed in each file.

I am now looking to use the Master file (which contains the code from Ron De Bruin) as a template for creating new project files.

I have got as far as creating the file, input or generation of file name parameters to create the variable NewFile and then:

ActiveWorkbook.SaveCopyAs NewFile  to create the new file and then re-open the NewFile because SaveCopyAs doesn't leave it open.

I am now stuck. In the file created, because it came from the Master file, it has all the VBA Modules that were written while tweaking this Master. I have copied all those that aren't needed as such in this file to my Personal.xlsb leaving the following:

Module 1
Function for Concatenating comments for onward link to a summary file (reason for Original question above)
Two Subs for inserting rows and ensuring formulas are copied down correctly

The above are all on one Module which I have also Exported for future Import using the Routine from Martin/Ron.

Module 2
The routine for creating the Copy file, as decribed above.

Module 3
Import and Export Module Routines from Martin.

With the Exported Module and the ImportModules routine I have been able to successfully update all of the existing files on a number of occasions but it is now that I am trying to apply it to a file that was created from the Master that I am having problems.

In the final Copy File I want to only have the Function/Subs of Module 1 so was thinking I could use the Import Modules routine previously used to delete all of the above from the file created and then Import the Exported Module 1 containing only the Function and Subs required.

However, when I call the ImportModules routine in my Create New File routine Excel just freezes and eventually goes to Not Responding and I have to use Task Manager to close it.

The NewFile code goes as follows:

    'Various Inputs or Calculations to get the parameters to fulfill NewFile
    NewFile = FilePath & "\" & FileDate & "-FOO_" & FY & "_" & ProjName & "_V0_1_" & ProjArea & "-OS.xlsm"
    ActiveWorkbook.SaveCopyAs NewFile
    Workbooks.Open Filename:=NewFile, UpdateLinks:=1
    ActiveSheet.Shapes.Range(Array("Bevel 3")).Delete
    Range("F5") = ProjName
    Call ImportModules

Open in new window

Up to Line 6 of the code works fine.

Line 5: Bevel 3 is just the button that I have put on the Master file main sheet with the "Create New" macro assigned to it, not needed in Final file so gets deleted.

Is it Freezing on the Call statement because it is confused as to which version of the ImportModules to use, there is effectively two copies open when it gets to that point, the copy in Master File and the copy in the New File which was copied from the Master. If there was ambiguity, I would have thought I would get an error message telling me that it didn't know which routine to use.

The same happens if I try and run the ImportModules routine manually when I have the NewFile reopened, making sure I select the Master File routine listed in the Run Macro window.

What is the correct syntax to include the filename with the Call statement if that is what is required?

If that is not the issue then I am stumped!!

Many thanks for all assistance.

Rob H
LVL 35
Rob HensonFinance AnalystAsked:
Who is Participating?
Rob HensonConnect With a Mentor Finance AnalystAuthor Commented:
Hi Martin,

Thanks for coming back to me and apologies for the confusion.

Scenario that I am hoping to accomplish is as follows:
1) Project Manager (PM) opens Master File (possibly Read Only)
2) PM clicks a button to Create a New Project file
3) Various Input Messages for Project Name etc
4) Routine uses Inputs to create new file with standard naming convention using inputs provided
5) New file only contains the VBA Subs and Functions it requires; so no chance of PM running VBA scripts that could mess up the file, eg New File routine which would then use the Project File rather than the Master as the basis.

Steps 1 to 4 are working fine. A New File gets created as expected.

For step 5 I was looking to use the ImportModules routine you provided previously to clear out all VBA modules from the New File and then Import a previously exported Module containing only the Subs and Functions required.

The existing project files and the Master are all saved in a shared network location and the Module with the latest version of Subs and Functions is also saved there.

The PM Team hot-desk so won't necessarily be at the same desk each day so no point having a local installation of an add-in and PMs probably won't entertain something being added to their Personal.xlsb files

How does these sound as options:

Master File only contains the Subs and Functions required
Create New File routine is in a separate "Code" file and the button in Master is linked to the separate file.
Will Code File have to be open for the Create New routine to work or will clicking the button open it?
If it needs to be open then I could put a WorkbookOpen statement in the Master file but that then takes us back to the original problem of getting rid of that statement.
If clicking the button will open the code file, I need that to be without any Alerts so the PM gets a seamless process.
Routine deletes the button so I assume that will break the link to the Code file.

Option 2
When the PM needs a new file they open a "Tools" file from the shared location. This file has an option for creating a new file; that routine will then open the Master file and save it based on inputs from the Tools file.

I think I like the idea of Option 2 and only thought of it while typing this so any suggestions or pitfalls to watch out for greatly appreciated.

Martin LissOlder than dirtCommented:
I'm confused by
I have got as far as creating the file, input or generation of file name parameters to create the variable NewFile...

Are you saying that the Master contains code that you don't want to be in the new template? If so why not delete what you don't need and save the resulting workbook under a new name and use that new workbook as the template, keeping the original Master for whatever purpose you desire?
Rob HensonFinance AnalystAuthor Commented:
I am currently working on Option 2 and it looks like it is going to work.

I believe I will be able to have a copy of the ImportModules routine in my "Tools" file and when there are changes to the Subs or Function, I will make the changes in the Master and Export a copy of the individual Module. I can then use the ImportModules routine in the Tools file to implement those changes across existing files.

Seems like it is all coming together.
Martin LissConnect With a Mentor Older than dirtCommented:
Sounds like you have it well in hand. Let me know if you need any further help.
Rob HensonFinance AnalystAuthor Commented:
Resolved it on my own.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.