Change sheet references in cell formula for large number of sheets

0
votes

I have a large number of raw data sheets (called Data1, Data2 etc) and I want to create a large number of clean data sheets (Clean1, Clean2 etc) that manipulate that data. Each sheet Clean'i' only references the data in the file Data'i'.

I wrote a macro to create many copies of sheet Clean1 that are named Clean2, Clean3 etc and what I would now like to do is replace "Data1" in the formula by "Data2" in sheet Clean2, and for all other sheets.

If it were just for one worksheet I would probably do something like this:

For Each cell In Range("A1:E9")
  If cell.Formula = "Data1" Then _
     cell.Formula = "Data2"
Next cell
End Sub

But I don't know how to create a loop that says that if I am at sheet Clean3 I want to replace Data1 by Data3 (i.e. essentially referencing part of the name of the worksheet I am looping over and use that in the expression I am replacing)

1
excelvba

1 Answers

2
votes

Iterate the sheets and use Replace:

Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
    If wks.Name Like "Clean*" Then
        wks.Range("A1:E9").Replace "Data1", "Data" & Replace(wks.Name, "Clean", "")
    End If
Next wks