Can only reference array table for Find/Replace macro from within the ActiveWorkbook??

[wittonlin]

I'm using a Find and Replace macro that refers to an array table.

I just HAVE to be missing something. There's NO way that the only way to reference an array table that stores values for Find/Replace macro is from only in the ActiveWorkbook I'm currently working; where I have to copy and paste the table over into a new sheet for every new Workbook that I want to run this macro! No way.

I've looked everywhere and can't believe that this is not a more common issue, or I'm totally missing the boat!

I really want to be able to use and refer to my Personal.xlsb default template for the array table, because it’s ALWAYS open, of course. How can I set up this reference?

This is the current reference from macro below and only way I can get it to work:

'Create variable to point to your table 
Set tbl = Worksheets("Table1").ListObjects("Table1")

(Referring to a sheet inside the ActiveWorkbook)


Here’s what I’ve tried instead, in an effort to reference another file, even location:

Set tbl = Personal.xlsb("Sheet1").ListObjects("Table1") 
------ 
I thought I had it With this one For sure!! 
Set tbl = Workbooks("Personal.xlsb").Sheets("Table1").ListObjects("Table1") 
------- 
Set wb1 = Application.Workbooks("Personal.xlsb") 
Set tbl = wb1("Table1").ListObjects("Table1") 
-------
Set wb1 = Workbooks("Personal.xlsb").Sheets("Table1") 
Set tbl = wb1("Table1").ListObjects("Table1") 
------- 
Set wb1 = Workbooks("Personal.xlsb").Sheets("Table1") 
Set tbl = wb1.ListObjects("Table1") 
-------

I’ve even tried the absolute file path, nothing.

As soon as I change it to try to refer to Personal.xlsb or location I get Error 9 : subscript out of range


Right now my Array Table MUST be copied into the Activeworkbook on the ‘Table1’ Sheet (Tab), every time I want to use this macro!

Sub FindReplace_Multi_ActivesheetOnly() 
     'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
    Dim sht As Worksheet 
    Dim fndList As Integer 
    Dim rplcList As Integer 
    Dim tbl As ListObject 
    Dim myArray As Variant 
     
    Application.Calculation = xlCalculationManual 
    Application.ScreenUpdating = False 
    Application.DisplayStatusBar = False 
    Application.EnableEvents = False 
    ActiveSheet.DisplayPageBreaks = False 
     
     'Create variable to point to your table
    Set tbl = Worksheets("Table1").ListObjects("Table1") 
     
     'Create an Array out of the Table's Data
    Set TempArray = tbl.DataBodyRange 
    myArray = Application.Transpose(TempArray) 
     
     'Designate Columns for Find/Replace data
    fndList = 1 
    rplcList = 2 
     
     'Loop through each item in Array lists
    For x = LBound(myArray, 1) To UBound(myArray, 2) 
         'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
         'For Each sht In ActiveWorkbook.Worksheets
         'If sht.name <> tbl.Parent.name Then
         
        ActiveSheet.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _ 
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ 
        SearchFormat:=False, ReplaceFormat:=False 
         
         'End If
         'Next sht
    Next x 
     
End Sub

 

[JoeMo]

There's only one sheet in Personal.xlsb. Unless you've done something I'm not familiar with, its not named Table1.

Try this (should work if Personal is hidden or not).

Sub Test()
Set tbl = Workbooks("Personal.xlsb").Sheets(1).ListObjects("Table1")
MsgBox tbl.Name
End Sub

 

[wittonlin]

Thank you so much for trying!

Unfortunately still got error 9 script out of range.

I can't believe this. It just makes no sense that I'd have to copy/create the table in every activeworkbook I want to run this.

btw it appears I can create and save any sheets I need in our .xlsb template.

 

[wittonlin]

Here's a few few details from the VBA Editor about the workbook (template) I'm trying to reference:

1) VBAProject (Personal.xlsb) [Which is the file name of my template]
2) Sheet1 (PERSONAL) [Next entry]
3) Sheet2 (Table1) [Then the table I'm trying to refer to]

NOTE: What's odd is using the (CTRL+G) from Sheet2 (Table1) -- It says 'Table13' ??


So does anyone think that I need to use all three references here:
'Create variable to point to your table
Set tbl = Workbooks("Personal").Sheets("Table1").ListObjects("Table1")

 

[wittonlin]

I swear, lately, I answer more of my own questions here than get answered.

FINALLY! With some help and feedback here and there, we figured it out!

The main issue, honestly, was knowing the TRUE reference NAMES. 2nd was the proper syntax. I was really close there from the beginning.

The weird part was learning that the table name (ListObject) was not what I'd thought. There's several ways to find out from highlighting the table and clicking the design tab that appears or just use (CTRL+G) from your target sheet. I found that the table was actually named Table13, not Table1 and without this correct none of the syntax mattered!

This was the proper vba coding syntax I needed to add/change, though I'm certain it's not the only way. This was the key: ("Table13")

    Dim wb As Workbook
    Set wb = Workbooks("Personal.xlsb")
    
    'Create variable to point to your table
      Set tbl = wb.Worksheets("Table1").ListObjects("Table13")