VBA シート名が一致した場合の転記内容について

閲覧頂きありがとうございます。
タイトルの通り、シート名が一致した場合の転記内容がうまく行かず躓いています。
教えて頂けると幸いです。
やりたい事はマスターデータのシート名と転記先のシート名が一致した場合、セルに入力されている値を転記先へコピーすることです。
よろしくお願いいたします。

Option Explicit

Sub マスターデータ取り込み02() '選択したファイルを取り込み、別のファイルに貼り付ける

Dim RC As Integer
Dim OpenFileName, fileName, Path, SetFile As String
Dim wbMoto, wbSaki As Workbook
Dim sh1name As String
Dim i As Long
Dim flag As Boolean

sh1name = ActiveSheet.Name '現在開いているシート名

Set wbMoto = ActiveWorkbook 'マスターデータ取り込み元をセット

Application.DisplayAlerts = False '応答メッセージの非表示

RC = MsgBox("Excel Bを開きますか?", vbYesNo + vbQuestion, "確認")
If RC = vbYes Then 'もしRCのメッセージにYesと答えたら

OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
'ダイアログボックスを表示して、マスターデータファイルを指定します。

If OpenFileName <> "Fales" Then 'もし指定したファイルがFalseではなかったら
SetFile = OpenFileName 'マスターデータファイルの指定=SetFile
Else '指定したファイルがなかったら
MsgBox "キャンセルされました" 'キャンセルされましたというメッセージボックスが表示される
Exit Sub 'マスターデータの取り込みをキャンセル
End If
Workbooks.Open fileName:=SetFile, ReadOnly:=True, UpdateLinks:=0 'マスターデータファイルを読み取り専用で開く
'ダイアログボックスで指定したExcel Bを開きます。

Set wbSaki = Workbooks.Open(Path & SetFile) '転記元のシート検索

For i = 1 To ActiveWorkbook.Worksheets.Count 'アクティブブックのシートを全てカウント
If Worksheets(i).Name = sh1name Then 'もし転記元のシートの名前と現在開いているブックのシートの名前が一緒だったら
flag = True
End If
Next i
If flag <> True Then 'フラグが"True"ではなかったら
MsgBox sh1name & "シートはありません。", vbInformation 'シートはありませんの情報メッセージアイコンが出る
wbSaki.Close False 'ExcelBのファイルを閉じる
End If

'転記内容

wbSaki.Worksheets(i).Range("G2:N32").Copy
wbMoto.Worksheets(sh1name).Range("G2:N32").PasteSpecial xlPasteFormulasAndNumberFormats


Application.CutCopyMode = False 'コピー切り取りを解除
wbSaki.Close False 'マスターデータ取り込み先のファイルを閉じる
Else
MsgBox "処理を中断します"
End If
Application.DisplayAlerts = True
End Sub

質問者からの補足コメント

• flag = True　の後に
  wbSaki.Worksheets(i).Range("G2:N32").Copy
  wbMoto.Worksheets(sh1name).Range("G2:N32").PasteSpecial xlPasteFormulasAndNumberFormats
  
  を実行しましたが、オブジェクト変数またはwithブロック変数が設定されていませんとエラーが出ました。
  エラーを調べてみるとsetを付ける、インスタント化すると記載されているサイトを見つけましたが、よくわかりません。
  fujillin様がおっしゃるように、保持用の変数かExit Forで一回中断するのが良いのでしょうか…

    補足日時：2021/11/15 15:50

No.2 ベストアンサー 回答者： fujillin 回答日時： 2021/11/15 14:11

こんにちは

ざっと見ただけなので、確認はしていませんけれど・・・


＞転記内容がうまく行かず躓いています。
どこかで、エラーが発生しているのでしょうか？
それとも実行はできるけれども、思った結果とは違ってしまうとか？

「マスターデータ」というネーミングから想像すると、マスター（＝固定）なのではないかと思いますけれど、その割にはファイル指定ダイアログで選択しているようなので、意味がよく分からないですね。

等々の疑問は置いておくとして、気になったのは、
＞Workbooks.Open fileName:=SetFile,～～
＞Set wbSaki = Workbooks.Open(Path & SetFile)
と、同じファイルを2回開こうとしていること。
多分、エラーにはなりませんけれど、あまり意味があるとは言えませんし、変数pathってどこにも定義されていませんよね？
（通常は空白文字列として解釈されるので、エラーにはなりませんけれど意味不明）

エラーの原因になっているのは、
＞wbSaki.Worksheets(i).Range("G2:N32").Copy
ではないかと推測します。
この時の変数 i の値を確認してみてください。

多分、シート数を超えた値になっていて、シートを参照できないのではないかと想像します。
同じ名前のシートを見つけた際に、そのシート番号を保持しておきたければ、保持用の変数に値を代入しておくか、あるいは発見したときに Exit For などでループを抜けてしまうような制御にしておく必要があるように感じます。

※ デバッグ中は、Application.DisplayAlerts = False などとはせずにテストしたほうが、ちゃんと警告が表示されるので何かと便利かと思いますけれど・・・

この回答へのお礼

質問が上手く伝えられず申し訳ございません。
おっしゃる通り
wbSaki.Worksheets(i).Range("G2:N32").Copy
ここでエラーが起きていました。

マスターデータは固定なのですが、共有フォルダを使用しているのでpathが変わってくるのではないかと思うのですが、どうでしょうか？

おっしゃる通りpath定義してないですサイトを参考に組んでいるので、まだまだ理解不足ですみません。。
＞Workbooks.Open fileName:=SetFile,～～
＞Set wbSaki = Workbooks.Open(Path & SetFile)
こちらどちらかを削除しても問題ないという事でしょうか？

Application.DisplayAlerts = False などとはせずに
⇒ご指摘ありがとうございます。
こちら削除してやってみます。

保持用の変数や、Exit Forなど、ヒントありがとうございます。

いろいろ試してみます、ありがとうございます！

お礼日時：2021/11/15 15:21

No.1 回答者： Zincer 回答日時： 2021/11/15 13:41

何処で不具合（エラーなど）が生じるかが分かればもう少し詳しく解析できそうですが、

＞Application.DisplayAlerts = False '応答メッセージの非表示
のためにエラー表示が出ないのでしょう。
開発段階では使用しないことをお勧めします。

＞Workbooks.Open fileName:=SetFile, ReadOnly:=True, UpdateLinks:=0
と
＞Set wbSaki = Workbooks.Open(Path & SetFile) '転記元のシート検索
は同じファイルを２回開く操作をしているように見えます。

＞For i = 1 To ActiveWorkbook.Worksheets.Count '
＞・・・
＞Next i
＞wbSaki.Worksheets(i).Range("G2:N32").Copy
は完全にアウトです。
For_Nextが終了した時点でiは最終値＋１になっています。
ですから、Worksheets(i)は存在しないでしょう。
解決策は
flag = True
のところで転記操作を行えば良いのではないでしょうか？
また、このようにしないと
＞If flag <> True Then
＞・・・
＞End If
の後に転記操作を行っていますので必ず実行しようとします。

この回答へのお礼

詳しい解説ありがとうございます！
まさに
＞wbSaki.Worksheets(i).Range("G2:N32").Copy
ここでエラーが起きる状態でした！
教えられたやり方で試してみます！

ありがとうございます

お礼日時：2021/11/15 15:21