Workbook Reference Problem
 

I have a macro that generates a sub() that I use as a "macro template".
I then try to go and change the newly created sub template's name using
this code (below) - I borrowed and adapted from somewhere in the
internet. The worksheet "FileTrackingSheet" is opened. I jump from the
macro that just renerated the template to this RenameSub code. The
macro would not compile by itselft, but if I run it, it removes the
name of from the template sub and leaves the new name as "sub ()" which
gives me another compiling error. I think I am not able to reference
the workbook FileTrackingSheet correctly. Can someone take a look at my
code. Thanks. ~Pablo

Sub RenameSub()
Dim OldMacroName As String
Dim findtext As Long
Dim NewMacroName As String
Dim wbXL As Excel.Workbook

Set wbXL = Workbooks(FileTrackingSheet.xls).Select
Sheets(MSBs_Tracking).Select
NewMacroName = Range("B6").Text
OldMacroName = "ChangeNameofThisMacro"

With
ActiveWorkbook.VBProject.VBComponents("FileTrackin gCompanyList").CodeModule
On Error GoTo notfound
findtext = .ProcBodyLine(OldMacroName, prockind:=vbext_pk_Proc)
.ReplaceLine Line:=findtext, String:="Sub " & NewMacroName & "()"
End With
Exit Sub
notfound:
MsgBox "not found"
End Sub

Workbook Reference Problem
 

I figure it out. Thanks you all.


Pablo wrote:
I have a macro that generates a sub() that I use as a "macro template".
I then try to go and change the newly created sub template's name using
this code (below) - I borrowed and adapted from somewhere in the
internet. The worksheet "FileTrackingSheet" is opened. I jump from the
macro that just renerated the template to this RenameSub code. The
macro would not compile by itselft, but if I run it, it removes the
name of from the template sub and leaves the new name as "sub ()" which
gives me another compiling error. I think I am not able to reference
the workbook FileTrackingSheet correctly. Can someone take a look at my
code. Thanks. ~Pablo

Sub RenameSub()
Dim OldMacroName As String
Dim findtext As Long
Dim NewMacroName As String
Dim wbXL As Excel.Workbook

Set wbXL = Workbooks(FileTrackingSheet.xls).Select
Sheets(MSBs_Tracking).Select
NewMacroName = Range("B6").Text
OldMacroName = "ChangeNameofThisMacro"

With
ActiveWorkbook.VBProject.VBComponents("FileTrackin gCompanyList").CodeModule
On Error GoTo notfound
findtext = .ProcBodyLine(OldMacroName, prockind:=vbext_pk_Proc)
.ReplaceLine Line:=findtext, String:="Sub " & NewMacroName & "()"
End With
Exit Sub
notfound:
MsgBox "not found"
End Sub