エクセルマクロでシート名を別ファイルセルからひっぱりたい

Question

前提・実現したいこと

請求書.xlsmファイルの集計シートにあるリストのパスから前半.xlsmファイルの同名シートを開いてrange("A5")をコピーして、請求書.xlsmファイルの同名シートのA62にコピーをするコードを調べていますが、うまくいきません
間違っている箇所と修正点などを詳しくご教授ください

発生している問題・エラーメッセージ

エラーメッセージ


### 該当のソースコード
ソースコード
Sub コピー()

Dim i As Long
Dim strSheetName As String
Dim ws01 As Worksheet '集計
Dim ws02 As Worksheet '前半
Dim ws03 As Worksheet '後半

Set ws01 = Worksheets("集計") 'ワークシートの設定
Set ws02 = Worksheets("前半") 'ワークシートの設定
Set ws03 = Worksheets("後半") 'ワークシートの設定

For i = 2 To ws01.Cells(Rows.Count, "A").End(xlUp).Row   '2行目～最終データ行までループ
strSheetName = Cells(i, "A").Value    'セルの値を取得

Workbooks.Open Filename:="C:\Users\users\Desktop\新しいフォルダー (21)\前半.xlsm"
Workbooks("前半.xlsm").Activate
Workbooks("前半.xlsm").Worksheets(strSheetName).Range("A5").Copy
Workbooks("請求書.xlsm").Worksheets(strSheetName).Range("A62").xlPasteValues

Next

End Sub
```ここに言語名を入力
VBA

### 試したこと

ここに問題に対して試したことを記載してください。

### 補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[meg_]

コードは「コードの挿入」で記入しましょう。

Answer 1

Sub コピー()
    Dim i As Long
    Dim strSheetName As String
    Dim wb01 As Workbook  '集計
    Dim ws01 As Worksheet '集計
    Dim wb02 As Workbook  '前半
    Set wb01 = Workbooks("請求書.xlsm")
    Set ws01 = Worksheets("集計") 'ワークシートの設定
    Workbooks.Open Filename:="C:\Users\users\Desktop\新しいフォルダー (21)\前半.xlsm"
    Set wb02 = Workbooks("前半.xlsm")
    For i = 2 To ws01.Cells(Rows.Count, "A").End(xlUp).Row '2行目～最終データ行までループ
        strSheetName = ws01.Cells(i, "A").Value    'セルの値を取得
        wb01.Worksheets(strSheetName).Range("A62").Value _
            = wb02.Worksheets(strSheetName).Range("A5").Value
    Next
    Workbooks("前半.xlsm").Close
End Sub

Copy、PasteValue は、代入に変更しました。
試してないけど。

Comments

[urusibara]

参考になりました！
ご教授ありがとうございます！

Answer 2

こんにちは。
問題のソースコードは、For-Nextループの中でWorkbooks.openをしてしまっているからだと思います。

以下のコードは、ちょっと自分なりに整理したものです。これで動作しました。

１：for i=2 to.... ではなく、もっと簡略化するために for eachにしました。

２：workbooks.openすると、そのブックがactiveになってしまい、別のシート上でEnd(xlDown)とかをやろうとするときは、そのシートactiveにしないとうまくいかないで、意識的にこっちのworkbookをactivateさせたりしています。

ご参考になれば.....

Sub コピー()

        'Dim i As Long - 変数 i は不要になりました
        Dim strSheetName As String
        
        Dim ws01 As Worksheet '集計
        Dim ws02 As Worksheet '前半
        Dim ws03 As Worksheet '後半
        あ
        Dim wbZenhan As Workbook '前半.xlsmです
        Dim wbSeikyusho As Workbook '請求書.xlsmです
        
        'このコードがある"請求書.xlsm" を　wbSeikyusho とします
        Set wbSeikyusho = ThisWorkbook
        
        Set ws01 = wbSeikyusho.Worksheets("集計") 'ワークシートの設定
        Set ws02 = wbSeikyusho.Worksheets("前半") 'ワークシートの設定
        Set ws03 = wbSeikyusho.Worksheets("後半") 'ワークシートの設定
        
        '前半.xlsmを開きつつ、セットします
        Set wbZenhan = Workbooks.Open("C:\Users\users\Desktop\新しいフォルダー (21)\前半.xlsm")
        
        'Workbooks.openすると、そのブックがアクティブになってしまうので、
        'こちらの”請求書.xlsm"ブックをアクティベートしなおしています
        wbSeikyusho.Activate
        
        Dim r As Range 'for eachするための変数です

        For Each r In ws01.Range("A2", Range("A1").End(xlDown)) '2行目〜最終データ行までループ

                strSheetName = r.Value    'セルの値を取得
        
                '前半.xlsmにある、各シートのA5セルの内容を、請求書.xlsmにある、同名のシートのA62セルに転記する
                wbSeikyusho.Worksheets(strSheetName).Range("A62") = _
                wbZenhan.Worksheets(strSheetName).Range("A5").Value
        
        Next r
        
        '”前半.xlsm"を閉じます
        wbZenhan.Close

End Sub

Comments

[urusibara]

実行することができました！
ご教授ありがとうございます！

Answer 3

初めて投稿しますが、、
strSheetName = Cells(i, "A").Value　で代入したシート名は前半.xlsmを開いたシート名ですか？
もしそうであっても、次のシート名はどうでしょう？
Workbooks("前半.xlsm")内のシートを確認して、あれば実行、なければ、、、のようにする必要があると思いますが、、

Dim TrgSht As Worksheet
For Each TrgSht In Workbooks("前半.xlsm").Worksheets'前半.xlsmのすべてのシートオブジェクトに対し
If strSheetName = TrgSht.Name Then 
'あった場合処理
End If
Next TrgSht

Comments

[urusibara]

参考になりました！
ご教授ありがとうございます！

Answer 4

間違っている箇所と修正点

えっと、前提条件として、エクセルのファイルを開くと、
エクセルから見たら、それは「ブック」になります。
ブックの中にはいくつかの「シート」があり、
シート上には多数のセルが存在しているわけです。

請求書.xlsmファイルの集計シートにあるリストのパスから前半.xlsmファイルの同名シートを開いて

1）「請求書.xlsm」ブックの「集計」シートのセル上にリストがある
2）それのどのセルに、パス（ファイルのフルパス？フォルダーのパス？）があるかを指定し取得
3）そのファイルを開く
4）もし、開いたファイルのブック名が「前半.xlsm」なら
5）「前半」（ブックと同じ名前ではないですよね？）シート
（ブックには1つしかシートが無い？）を見て（開くのはファイルでシートは指定だけで良い）、
どのセルを見て値を取得して転記

まずはこれが出来るように書いてみてください。
この辺の前提条件などの理解があやふやなので、
上手くVBA語で表現出来ないのかなと思いました。

ループはこれを繰り返すだけなので、まずは1件を処理できるようになってください。

Comments

[urusibara]

参考になりました！
ご教授ありがとうございます！