VBAで特定のシートを別ファイルにコピーしたい

Question

**
どうやら原因は必要なシートをactivateしていなかった為でした。
7行目と8行目の間に
Workbooks("Profile List Maker.xlsm").Activate
と入れたらうまくいきました！
2021.2

**

VBA初心者です。
元ファイル（"C" & VVYear & "年度　新入社員データ.xlsx"）から「写真」という言葉が入っているシート名のシートだけを別ファイル("Profile List Maker.xlsm")にコピーしたいのですが、
8行目で「インデックスが有効ではありません」とメッセージが出てしまい、止まってしまいます。
（※コード上にカーソルをあてると、Workbooks("Profile List　Maker.xlsm").Worksheets(Worksheets.Count)＝「インデックスが有効ではありません」とコメントが出ます）
7行目のワイルドカードの設定がおかしいのでしょうか？
どなたかお力添えいただければ幸甚に存じます。

１：Option Explicit

２：Sub Profile_List_Maker_年度全員(ByVal VVYear As Integer)

３：Dim aa As String, pic As Integer
４：aa = Dir("C" & VVYear & "年度　新入社員データ.xlsx")

５：Workbooks.Open Filename:="C" & VVYear & "年度　新入社員データ.xlsx", ReadOnly:=True

６：For pic = 1 To Worksheets.Count
７： If Workbooks(aa).Worksheets(pic).Name Like "" & "写真" & "" Then
８： Workbooks(aa).Worksheets(pic).Copy after:=Workbooks("Profile List
Maker.xlsm").Worksheets(Worksheets.Count)
９：End If
１０：Next

１１： Workbooks(aa).Worksheets(VVYear & "年入社　新入社員情報").Copy
after:=Workbooks("Profile List Maker.xlsm").Worksheets(Worksheets.Count)

１２：Workbooks(aa).Close Filename:="C" & VVYear & "年度　新入社員データ.xlsx"

Answer 1

下記のコードでうまくいきました。
でも何で　Workbooks("Profile List Maker.xlsm").Worksheets(Worksheets.Count)　だとコピー先がうまく指定できなかったんだろう...？ブックも指定しているのに。
もしご存じの方がいらしたらご教示お願いします＾＾；

Option Explicit

Sub Profile_List_Maker_年度全員(ByVal VVYear As Integer)

Dim aa As String, pic As Integer

aa = Dir("C" & VVYear & "年度　新入社員データ.xlsx")

Dim bb As Worksheet
Set bb = Worksheets("作成画面")

Workbooks.Open Filename:="C\" & VVYear & "年度　新入社員データ.xlsx", ReadOnly:=True


 For pic = 1 To Workbooks(aa).Worksheets.Count
    If Workbooks(aa).Worksheets(pic).Name Like "*" & "写真" & "?" Then
        Workbooks(aa).Worksheets(pic).Copy after:=bb
    End If
Next

Workbooks(aa).Worksheets(VVYear & "年入社　新入社員情報").Copy after:=ThisWorkbook.Worksheets(Worksheets.Count)

Workbooks(aa).Close Filename:="C\" & VVYear & "年度　新入社員データ.xlsx"

Answer 2

ワークブックを指定せずに、Worksheets.Count とするとアクティブなワークブックのシート数になります。この時点でアクティブなのはコピー元のブックです。ここはコピー先のブックのシート数にしないとだめなはずです。

ワークブックの指定を省略せずにコードを書くべきです。省略すると操作対象が曖昧になり読みづらいコードになりますし、アクティブなオプジェクトが想定外に変更されたりするとバグの原因になります。

Option Explicit

Sub Profile_List_Maker_年度全員(ByVal VVYear As Integer)

    Dim pic As Integer            

    Dim fromWB As Workbook 'コピー元ブック
    Set fromWB= Workbooks.Open(Filename:="C\" & VVYear & "年度　新入社員データ.xlsx", ReadOnly:=True)

    Dim toWB As Workbook 'コピー先ブック
    Set toWB = Workbooks("Profile List Maker.xlsm")

    For pic = 1 To fromWB.Worksheets.Count
        If fromWB.Worksheets(pic).Name Like  "*" &  "写真" & "*" Then
            fromWB.Worksheets(pic).Copy after:=toWB.Worksheets(toWB.Worksheets.Count)
        End If
    Next

    fromWB.Worksheets(VVYear & "年入社　新入社員情報").Copy after:=toWB.Worksheets(toWB.Worksheets.Count)

    fromWB.Close

Comments

[退会済みユーザー]

返信が遅くなり申し訳ありませんでした。またご説明ありがとうございます。
早速頂いたコードを入れてみたのですが、Set fromWB= Workbooks.Open Filename:="C\" & VVYear & "年度　新入社員データ.xlsx", ReadOnly:=Trueで、「修正候補：ステートメントの最後」とメッセージが出てしまいました。上記だとオブジェクトと認識されないのでしょうか？

[hatena19]

あっ、間違えてました。代入するときは()が必要でした。下記でどうでしょうか。

Set fromWB = Workbooks.Open(Filename:="C\" & VVYear & "年度　新入社員データ.xlsx", ReadOnly:=True)

[退会済みユーザー]

ご教示ありがとうございます。そちらも試したのですが、やはりダメでした。
外部参照のファイルはオブジェクト変数に入れられないのでしょうか...？
（ネットや本で調べてみたのですが、載ってませんでした；）

Answer 3

「Workbooks("Profile List Maker.xlsm").Worksheets(Worksheets.Count)」でエラーが出ている気がします。
"Profile List Maker.xlsm"にWorksheets.Count枚目のシートは存在していますか？

Comments

[退会済みユーザー]

返信が遅くなってしまい、申し訳ありません。
ご指摘の箇所で出ています。hatena19さんのアンサーで理解したのですが、
Worksheets(Worksheets.Count)の前にWorkbooks("Profile List Maker.xlsm")を記述しても、
Workbooks("Profile List Maker.xlsm")の一番右端のシートを意味する訳ではないようですね...。
別ファイル：Workbooks("Profile List Maker.xlsm")に元ファイル：（"C\" & VVYear & "年度　新入社員データ.xlsx"）の一番右端のシートはコピー前なので、存在していません。

[meg_]

今現在Activeなもの（WorkbookとかWorksheetとか）が何かを意識してコードを書かないと、意図しない動作となることがあります。エラーが出たり、意図しない動きになったときには丁寧にデバッグしましょう。

[退会済みユーザー]

コメントありがとうございます。なんとか自己解決できました。
元ファイルからコピーするシートは下記の二つです。

1.シート名に「写真」という言葉が入っているシート
2.リストの作成元となるデータシート

両シートのコピー先を Workbooks("Profile List Maker.xlsm").Worksheets(Worksheets.Count)としていたのが良くなかったようです。
1.のシートコピー時は別ファイルの3シート目、2.のシートコピー時は別シートの4シート目が指定されるはずなのですが、1.2.ともに元ファイル？の７シート目が指定されてました。
1.のシートのコピー先を別ファイルのシート名＜Worksheets("作成画面")＞で指定したところ、
VBAが区別して認識できたようです。
でも結局、何故うまく指定できていなかったのかは分かりません。

[meg_]

Worksheets(Worksheets.Count)はシート名ではなく、インデックスでシートを指定しています。
Worksheets.CountにはWorkbookの指定がないため、Activeworkbook.Worksheets.Countと同じになり、それはActiveworkbook（"C\" & VVYear & "年度　新入社員データ.xlsx"）.Worksheets.Countってことかと。