【VBA】複数ブックから特定のシートを抽出して一つのブックに集約するマクロについて

あるファイル内の複数ブックから特定のシートを抽出して、一つのブックに集約する(親ブックの末尾に貼り付ける)マクロを作っていますが、エラーになってしまいます。（私の力量では原因が特定できませんでした）

エラーの内容は、後ろから5行目の「fm.」の後の「Worksheets 」に対して「コンパイルエラー　プロパティの使い方が不正です」というメッセージが出るものです。

Option Explicit
Sub 集計マクロ()

Dim fPath As String, fName As String, fm As Workbook
fPath = "C:¥Users¥nm¥Desktop¥マクロテスト¥"
fName = Dir(fPath & "¥**_test.xlsx")
fm = Dir(fPath & "¥集計マクロ.xlsm")

Do While fPath & fName <> ""
Workbooks.Open (fPath & fName)
ActiveWorkbook.Worksheets("sheet1"). _
CopyAfter: fm.Worksheets (Worksheets.Count)
ActiveWorkbook.Close SaveChanges:=False
fName = Dir()
Loop

End Sub

No.3 回答者： Qchan1962 回答日時： 2022/09/04 23:17

#2です　見直しに来ましたら、、

脱線して、肝心な　コンパイルエラー　プロパティの使い方が不正です
の部分を書いていませんでした。（書いたつもりで・・）

fmはブックとして宣言されていますので
fm.Worksheets(Worksheets.Count)に問題がある訳ではなく
=が無い事が理由で
Worksheetsはコンパイルエラーが返ります（文法が間違っている）

単体で（意味はありませんが）
ActiveWorkbook.Worksheets (Worksheets.Count)
と書いた場合コンパイルエラーです

単体で
ActiveWorkbook.Worksheet
や
ActiveWorkbook.Worksheets (Worksheets.Count).Range
と書くと実行時エラーです（コンパイルできてしまう）

実行時エラーは
CopyAfter:　や　fm = Dir(fPath & "¥集計マクロ.xlsm")　
（＃２に挙げた部分）などがあります

オブジェクトによって　コンパイルエラー、実行時エラー　
構造などの絡みがあるのかも知れませんが、VBAはこの辺りが？と思います

良く見直せば自身で直せると思いますよ

この回答へのお礼

ご丁寧な回答、ありがとうございます！大変参考になります。ご指摘の部分について、自分でももう一度よく見直してみようと思います。

お礼日時：2022/09/06 00:29

No.2 回答者： Qchan1962 回答日時： 2022/09/04 19:20

こんばんは

サンプルコードはすでに回答されていますが

＞エラーになってしまいます。（私の力量では原因が特定できませんでした）エラーの内容は、後ろから5行目の「fm.」の後の「Worksheets 」に対して「コンパイルエラー　プロパティの使い方が不正です」というメッセージが出るものです。

いくつかの問題点があります
fm As Workbookはワークブックを示す変数宣言でオブジェクトになります
オブジェクト変数にオブジェクトを代入する場合
Set 変数 =　オブジェクト　とする必要があります
また、
Dir(fPath & "¥集計マクロ.xlsm")
Dir関数は対象フルパスにオブジェクトが存在するか調べる関数で
存在する場合は、対象オブジェクト（一番右書いた）を文字列で返します
無い場合は""が返ります

Sub test()
MsgBox Dir("C:¥Users¥nm¥Desktop¥マクロテスト" & "\集計マクロ.xlsm")
End Sub

＞ "¥**_test.xlsx"
ワイルドカードアスタリスクは任意数の文字なので1つで良いと思います

＞fPath = "C:¥Users¥nm¥Desktop¥マクロテスト¥"
最後に¥付けていますが
これは、実行時エラーが返る事は無いでしょうけれど
"C:¥Users¥nm¥Desktop¥マクロテスト¥¥集計マクロ.xlsm"
となりますので一応指摘しておきます

一応の意図は、こちらを参考にしてください
https://oshiete.goo.ne.jp/qa/4971363.html

No.1 回答者： tatsumaru77 回答日時： 2022/09/04 17:21

集計マクロ.xlsmに集計マクロが登録されていて、

この集計マクロ.xlsmの末尾に*_test.xlsxのsheet1を貼り付ける
ということで良いでしょうか。
その前提でよいなら、以下のようにしてください。

Sub 集計マクロ()

Dim fPath As String, fName As String, wb As Workbook
fPath = "C:\Users\nm\Desktop\マクロテスト\"
fName = Dir(fPath & "\*_test.xlsx")
Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
wb.Worksheets("sheet1").Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
wb.Close SaveChanges:=False
fName = Dir()
Loop

End Sub

この回答へのお礼

無事作動しました！ありがとうございました！

お礼日時：2022/09/06 00:26