CODEWITHSUNDEEP
excelvbaloops
Shae1999
Shae1999

Reputation: 41

Reference a different workbook

I am using the guidance here: Find and replace in a loop using macro in Excel

I have a workbook "TLA Lookup.xlsx" saved in a shared location. It has a sheet named TLAs. Column A consists of a list of TLAs and column B is the corresponding business name.

I have a macro to check the TLA Lookup workbook and wherever the TLA exists, replace it with the business name. I can do this within the same workbook.

I have different workbooks where I want this find/replace to happen. I have to copy the TLAs sheet from the TLA Lookups workbook over every time. I want to reference that workbook automatically instead.

How do I reference the TLA Lookup as being the workbook the macro needs to look in for the find/replace text?

Sub find_replace_2()
    Dim TLA As String
    Dim NAME As String
    Dim i As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
   
    'Open the Workbook that has all of the TLAs and CI Names from the K drive,
    ' so now both workbooks are open

    Workbooks.Open Filename:= _
      "K:\CLE01\Team_QA\Upcoming Change Highlights\TLA Lookup.xlsx"
        
    Set wb = TLA Lookup.xlsx    ' <----  Here is where I get a syntax error
    Set sht1 = wb.Sheets("TLAs")  

    For i = 1 To 4000
        TLA = wb.sht1.Range("A" & i).Value
        NAME = wb.sht1.Range("B" & i).Value

        Selection.Replace What:=TLA, replacement:=NAME _
          , LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
          :=False, ReplaceFormat:=False            
    Next i

End Sub

Upvotes: 4

Views: 78

Answers (1)

PeterT
PeterT

Reputation: 8557

You can easily do this, but you have to understand the difference between ThisWorkbook and the ActiveWorkbook. (See explanations here, here, and here for some guidance.)

With that knowledge, just be clear about which workbook you're referencing in your code:

EDIT: expanded code in the example below

Option Explicit

Sub find_replace_3()
    Dim tlaLookupWB As Workbook
    Dim tlaSheet As Worksheet
    Set tlaLookupWB = Workbooks.Open(Filename:= _
        "K:\CLE01\Team_QA\Upcoming Change Highlights\TLA Lookup.xlsx")
    Set tlaSheet = tlaLookupWB.Sheets("TLAs")
    
    '--- determine how many rows of TLAs exist
    Dim numberOfTLAs As Long
    With tlaSheet
        numberOfTLAs = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
    '--- replacements will be made in the currently active workbook
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
    '--- now check all of the TLAs and make replacements if found
    Dim i As Long
    For i = 1 To numberOfTLAs
        Dim tla As String
        Dim name As String
        tla = tlaSheet.Cells(i, 1).Value
        name = tlaSheet.Cells(i, 2).Value
            
        '--- search all of the worksheets in the current workbook
        '    and replace the tla with the name
        Dim ws As Worksheet
        For Each ws In wb.Sheets
            ws.Cells.Replace What:=tla, Replacement:=name, _
                             LookAt:=xlWhole, SearchOrder:=xlByRows, _
                             MatchCase:=False, SearchFormat:=False, _
                             ReplaceFormat:=False
        Next ws
    Next i
    
    tlaWB.Close SaveChanges:=False
End Sub

Upvotes: 2

Related Questions