-
Expert
61 Answers
- Posted on Dec 05, 2008
Re: I have a runtime error 9 subscript out of range, when...
- Start the Visual Basic Editor (press ALT+F11).
- If Project Explorer is not visible, press CTRL+R to activate Project Explorer.
- Click to select the Visual Basic project that contains the module sheet that you want to copy.
- On the Tools menu, click References. Click to select the Microsoft Visual Basic for Applications Extensibility 5.3 check box, and then click OK.
- Click Module on the Insert menu.
- Type the following code into the module sheet:
Sub CopyModule()
Dim CodeLines As String
Dim ModuleToCopy As VBComponent
Dim NewModule As VBComponent
' Set a variable to the module to copy.
' Note: This assumes that the name of the module to copy is
' "module2". Replace "Module2" with the name of the module to copy.
Set ModuleToCopy = _
Application.VBE.ActiveVBProject.VBComponents("module2")
' Get the Visual Basic code from the module.
CodeLines = ModuleToCopy.CodeModule.Lines _
(1, ModuleToCopy.CodeModule.CountOfLines)
' Create a module sheet in another workbook.
' Note: You must refer to the name of the Visual Basic project. By
' default, the name of all new projects is "VBAProject". You may want
' to rename the project to which you are copying the contents of the
' module sheet.
Set NewModule = Application.VBE.VBProjects("VBAProject") _
.VBComponents.Add(vbext_ct_StdModule)
' Add the Visual Basic code to the new module.
NewModule.CodeModule.AddFromString CodeLines
' Rename the new module to the name of the previous module.
NewModule.Name = ModuleToCopy.Name
End Sub
- Run the CopyModule procedure.
Regards
Samson Ved
×