エクセルで置換リストを別ブックにおいたマクロを作りたい

質問

置換専用につくったワークシートに
A列に検索文字
B列に置換文字を入力したリスト（例えば"Book2.xls"の"sheet1"）を作りました。
このリストを使って別のブック内（例えば"Book1.xls"）の複数のシート内を一括して置換えがしたいです。
自分で調べてみて下記で置換えはできたのですが、その都度、各シートを選択しなければだめでした。
一括で同ブック内の複数シート内を置換えさせるには、どこを修正したらいいのでしょうか？

見よう見まねの初心者です。
どうぞよろしくお願いします。

Sub 置換()
　With ThisWorkbook
　　If ActiveSheet Is .Worksheets(1) Then Exit Sub
　　For i = 1 To .Worksheets(1).Range("A65536").End(xlUp).Row
　　　ActiveSheet.Cells.Replace _
　　　　 What:=.Worksheets(1).Range("A" & i).Value, _
　　　　 Replacement:=.Worksheets(1).Range("B" & i).Value, _
　　　　 LookAt:=xlPart, SearchOrder:=xlByColumns
　　Next
　End With
End Sub

回答No.3

n_na_tto · ベストアンサー

No.2さんのおっしゃることで完璧です。

コードが書かれているブックのA:B列の複数置換条件をもとに
E:\Office\Excel\Book1.xlsの各ワークシートを置換する例です。

Sub 置換1()
Dim cntRow As Long
Dim myRng As Range
Dim myBook As Workbook
Dim mySht As Worksheet
Dim i As Long
　'置換範囲をRangeオブジェクトmyRngに代入
　With ThisWorkbook.Worksheets(1)
　　cntRow = .Range("A" & Rows.Count).End(xlUp).Row
　　Set myRng = .Range("A1:B" & cntRow)
　End With
　'対象ブックOpen
　Set myBook = Workbooks.Open("E:\Office\Excel\Book1.xls")
　'対象ブックのワークシートをループ
　For Each mySht In myBook.Worksheets
　　For i = 1 To cntRow
　　　If Len(myRng(i, 1).Value) > 0 Then
　　　　mySht.Cells.Replace _
　　　　　 What:=myRng(i, 1).Value, _
　　　　　 Replacement:=myRng(i, 2).Value, _
　　　　　 LookAt:=xlPart, SearchOrder:=xlByColumns
　　　End If
　　Next i
　Next
End Sub

※上のコードの変形です...

Sub 置換2()
Dim cntRow As Long
Dim myVar As Variant
Dim myBook As Workbook
Dim mySht As Worksheet
Dim i As Long
　'置換パターンを配列myVarに代入
　With ThisWorkbook.Worksheets(1)
　　cntRow = .Range("A" & Rows.Count).End(xlUp).Row
　　myVar = .Range("A1:B" & cntRow).Value
　End With
　'対象ブックOpen
　Set myBook = Workbooks.Open("E:\Office\Excel\Book1.xls")
　'対象ブックのワークシートをループ
　For Each mySht In myBook.Worksheets
　　For i = 1 To cntRow
　　　If Len(myVar(i, 1)) > 0 Then
　　　　mySht.Cells.Replace _
　　　　　 What:=myVar(i, 1), _
　　　　　 Replacement:=myVar(i, 2), _
　　　　　 LookAt:=xlPart, SearchOrder:=xlByColumns
　　　End If
　　Next i
　Next
End Sub

回答No.2

tarinko_06

for each objBook in Workbooks
for each objSheet in objBook.worksheets
処理
next
next

「for each」はinで指定した範囲内をぐるぐる回ります。

回答No.1

imogasi

検索・置換にはブック単位というのが在る。「検索場所」を「ブック」を択ぶ。
そして置換操作をしてマクロの記録を採る。
ーー
それを
Set wb = Workbooks("2.xls").Worksheets("sheet2")
What:=wb.Cells(i, "A"),
Replacement:=wb.Cells(i, "B"),
の部分を変えたものが下記である。
ーーー
すると下記のようなコードになる。
Sub Macro1()
Set wb = Workbooks("2.xls").Worksheets("sheet2")
For i = 1 To 2
Cells.Replace What:=wb.Cells(i, "A"), Replacement:=wb.Cells(i, "B"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub
ーー
Workbooks("2.xls")は開いておくこと。
Set wb = Workbooks("2.xls").Worksheets("sheet2")
のA1,B1には置換前と置換後の対応語句を入れておく。
A2,B2以下も同じ。
業が増えれば
Worksheets(1).Range("A65536").End(xlUp).Row
式で最下行を検出する。前にブック限定のコードwb.をつけることと
Worksheets(1).を2とかに変える。
Sheet2にしたのはこちらの都合。意味なし、ごめん。
上例では私の場合旨く行くようだ。