Using active sheet instead of using sheet name in a two function code

[shadow6810]

Hi, I want to use the code below without having to write out the sheet name in Sheets("Sheet1").Activate in the Public Sub function, so I can just click on the sheet and run the code. I am unfamiliar with VBA language, and this should be a simple fix, but after an hour of trying things out, I can't get it working so I'm asking here now. Any help would be appreciated!

Public gcolWords As New Collection

Public Sub ReplaceAllWrds()
Dim vWord, vAbv, itm
Dim i As Integer
Dim Lastrow As Integer

LoadAbbrevs

Sheets("Sheet1").Activate
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("H2:J" & Lastrow).Select
For Each itm In gcolWords
   i = InStr(itm, ":")
   vWord = Left(itm, i - 1)
   vAbv = Mid(itm, i + 1)
   Replace1Wrd vWord, vAbv
Next
Set gcolWords = Nothing
End Sub

Private Sub Replace1Wrd(ByVal pvWrd, pvAbv)
On Error Resume Next


    Selection.Replace What:=pvWrd, Replacement:=pvAbv, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Private Sub LoadAbbrevs()
Dim vWord, vAbv, vItm

Sheets("abbrevs").Activate
Range("A2").Select
While ActiveCell.Value <> ""
   vWord = ActiveCell.Offset(0, 0).Value
   vAbv = ActiveCell.Offset(0, 1).Value
   vItm = vWord & ":" & vAbv
  
   gcolWords.Add vItm
   ActiveCell.Offset(1, 0).Select   'next row
Wend
End Sub

 

[mehidy1437]

Remove or comment out these two lines & check.
Sheets("Sheet1").Activate
Sheets("abbrevs").Activate

 

[shadow6810]

Remove or comment out these two lines & check.
Sheets("Sheet1").Activate
Sheets("abbrevs").Activate

Removing the Sheets("abbrevs").Activate does not work as the code in the Private Sub now refers to the ActiveWorksheet (Sheet1) I think

 

[Alex Blakenburg]

The fact that it is private won't impact what is the activesheet.
Private does limit how you can access it. ie it won't appear in the List of Macros (Alt+F8) and as such you also can't assign it directly to a button.

If you want to leave it as Private you would need to set up a Public Sub that you can access which is in the same module and in turn runs the Private Sub.
It would be easier to just make it Public.

 

[GWteB]

Hi shadow6810, welcome to MrExcel!

... so I can just click on the sheet and run the code.

The code below targets the active worksheet. Code to be pasted in a standard module.
Your program logic with separate procedures as dependencies of the main procedure has been left intact intentionally. Then you can probably better see what has changed. See if this works for you.

Private Function GetAbbrevs(ByVal argSht As Worksheet) As Variant
    Dim Rng As Range
    With argSht
        Set Rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Resize(, 2)
    End With
    GetAbbrevs = Rng.Value
End Function

Private Sub Replace1Wrd(ByVal argRng As Range, ByVal pvWrd As String, ByVal pvAbv As String)
    On Error Resume Next
    argRng.Replace What:=pvWrd, Replacement:=pvAbv, LookAt:=xlWhole, _
                   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                   ReplaceFormat:=False
End Sub

Public Sub Shadow6810()
    Dim vWord, vAbv, itm
    Dim i As Long
    Dim Lastrow As Long

    Dim arr As Variant
    arr = GetAbbrevs(ThisWorkbook.Worksheets("abbrevs"))

    Dim raTarget As Range
    With ActiveSheet
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set raTarget = .Range("H2:J" & Lastrow)
        For i = LBound(arr, 1) To UBound(arr, 1)
            vWord = arr(i, 1)
            vAbv = arr(i, 2)
            Replace1Wrd raTarget, vWord, vAbv
        Next
    End With
End Sub