I have a spreadsheet that I need to anonymise for an audit (Usernames, hostnames, IP addresses etc..)
I'm trying to run a find and replace task on an entire workbook, I have created a lookup table that has a 'find' column (the data to be found) and a 'replace' column (the data that will replace the data in column one).
I've been using the below VB Script which works for a single worksheet but I need to search entire workbook (apart from one sheet which contains the lookup table), the workbook has 76 sheets in total.
I have referenced the single worksheet below, and I'm looking to replace this with an entry to reference all but one worksheets within the workbook. The lookup table is contained within the workbook on the final sheet so don't want this included in the 'find'.
Any help on this would be greatly appreciated. If you need any additional info please let me know. Many thanks in advance.
Sub abbrev() Dim abvtab() As Variant Dim ltsheet As Worksheet Dim datasheet As Worksheet Dim lt As Range Set ltsheet = Sheets("sheet2") ' REFERENCE TO SINGLE WORKSHEET Set datasheet = Sheets("ACTIVE_DIRECTORY_User") Set lt = ltsheet.Range("A2", ltsheet.Range("B2").End(xlDown)) abvtab = lt For i = 1 To UBound(abvtab) datasheet.Cells.Replace What:=abvtab(i, 1), Replacement:=abvtab(i, 2), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Next i End Sub