Find and Replace string within sheet macro using code in another macro

Question

I have a worksheet and worksheet related code called "GL Budget2". The code contains several instances of the string "GL Budget". This string must be renamed "GL Budget2" by using code from within another macro called FindAndReplace. My code (thanks to Pearson):

Sub FindAndReplace()

    Dim SL As Long, EL As Long, SC As Long, EC As Long
    Dim S As String
    Dim Found As Boolean

    With ThisWorkbook.VBProject.VBComponents("GL Budget2").CodeModule
        SL = 1
        SC = 1
        EL = 99999
        EC = 999
        Found = .Find("GL Budget", SL, SC, EL, EC, True, False, False)

        If Found = True Then
            S = .Lines(SL, 1)
            S = Replace(S, "GL Budget", "GL Budget2")
            .ReplaceLine SL, S
        End If

    End With
End Sub

It crashes with the subscript out of range error on the line

With ThisWorkbook.VBProject.VBComponents("GL Budget2").CodeModule

Any suggestions would be greatly appreciated.

Tim Williams · Accepted Answer · 2015-02-25 01:12:59Z

1

GL Budget2 is likely not the code name of your sheet, but the name on the sheet tab.

Try this:

Dim cn as string

cn = ThisWorkbook.Sheets("GL Budget2").CodeName

With ThisWorkbook.VBProject.VBComponents(cn).CodeModule
'...etc

answered Feb 25, 2015 at 1:12

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jmaz Over a year ago

Tim: GL Budget is the name of the original tab, and GL Budget2 is the name of the copy. GL Budget has event handler code that is exactly copied for GL Budget2, but I need to change the sheet name within the GL Budget2 code from "GL Budget" to "GL Budget2" in order for the code to point to the correct sheet and work correctly. When I insert your code, the error goes away, but the string substitution does not happen.

Tim Williams Over a year ago

Do you have the sheet name hard-coded in its code module? You could use Me.Name to replace that.

jmaz Over a year ago

Tim, your suggestion was in the ballpark and now it works, thanks.

Collectives™ on Stack Overflow

Find and Replace string within sheet macro using code in another macro

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related