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:
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.
The routine for creating the Copy file, as decribed above.
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"
Workbooks.Open Filename:=NewFile, UpdateLinks:=1
Range("F5") = ProjName
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.