1

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
0

It's just a simple matter of replacing the single worksheet reference with an extra outer loop:

Sub abbrev()

  Dim abvtab() As Variant
  Dim ltsheet As Worksheet
  Dim datasheet As Worksheet
  Dim lt As Range
  Dim i As Long

  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 Each datasheet In Worksheets
    If datasheet.Name <> ltsheet.Name Then
      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 If
  Next datasheet

End Sub

The lookup table sheet is excluded by name.

  • Thanks RobinCTS this worked and all sheets, excluding the sheet containing the lookup table, has been amended. Really appreciate the quick response. Many thanks. – Nicky G Jun 12 '18 at 13:38
  • @NickyG Glad to help. – robinCTS Jun 12 '18 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.