I'm using the Find and Replace macro (see below) that refers to an array table storing the reference data.
There's NO way that the only way to reference an array table and stored values for Find/Replace macro is from within any given ActiveWorkbook I'm currently working.
I need to be able to use and refer to my Personal.xlsb default template, because it’s ALWAYS open, to reference the Array Table, below.
How can I point the macro to the Array Table this way, so I don’t have to copy/create it into every new Workbook that I want to run this macro?
This is the current reference from macro below and the 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 I’m using.)
Here’s what I’ve tried instead, so far, in an effort to reference another file:
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 tbl = Workbooks("Personal.xlsb").Sheets(1).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 using strings above, I get Error 9 : subscript out of range
Right now my Array Table MUST be copied into every Activeworkbook on the ‘Table1’ Sheet (Tab), every time I want to use this macro!
Sub FindReplace_Multi_ActivesheetOnly()
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
'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