Excelマクロのデータの統合について

質問

下記は給料１年分の集計をマクロで記録した結果の一部です。
そこで質問ですが、シート名を１２行、列記しないで済む方法はありますでしょうか？

Selection.Consolidate Sources:=Array( _
"'C:\Users\\[給料.xlsm]28(1)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(10)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(11)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(12)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(2)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(3)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(4)'!R2C1:R20C8", _
"'C:\Users\[給料.xlsm]28(5)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(6)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(7)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(8)'!R2C1:R20C8", _
"'C:\Users\\[給料.xlsm]28(9)'!R2C1:R20C8"), Function:=xlSum, _
TopRow:=True, LeftColumn:=True, CreateLinks:=False

回答No.4

real beatin（@realbeatin） · ベストアンサー

こんにちは。
２つめの質問も不調だったみたいなので、
合わせてこちらでお応えします。

パスの指定についてですが、
　C:\Users\\[給料.xlsm]28(1)'!R2C1:R20C8
　C:\Users\[給料.xlsm]28(5)'!R2C1:R20C8
（これは例示の為に編集した際のミスだとは思いますが）
のような細かな相違にも注意深く編集作業して下さい。
特に気になる点として、
仮に、普通にシートのコピーを作成した場合のシート名には、
　28 (1)
のように、半角スペースが入るのがExcelの初期仕様ですので、
　28(1)
　28 (1)
のように、半角スペースで相違のあるシート名の混在が無いか
確認しておいた方が良いでしょう。
（必要なら指定したフォルダ名をエクスプローラーで開いて、
今現在の対象ブックの存否確認も）

　'C:\Users\\[給料.xlsm]28(1)'!R2C1:R20C8
の中身を部分的に捉えて、
　'ファルダパス[ブック名]シート名先頭(月)'!R2C1:R20C8
という風に頭の中で読み替えるようにして、
・ファルダパス
・ブック名（角括弧括りの徹底、拡張子不統一のチェック）
・シート名先頭
・シート名先頭と次の括弧との間のスペースの有無
の４点について、
マクロで指定しようとしているものと、実際のものとを、
よくよく相違がないように確認してください。

例示されたフォルダパス等は実際とは異なるものを示している筈ですから、
書換える際には、
　"'C:\Users\\[給料.xlsm]" & YY & "(" & i & ")'!R2C1:R20C8"
　"文字列値" & 変数 & "文字列値" & 変数 & "文字列値"
のように、
　文字列は、「"」ダブルクオート（二重引用符）で括ること、
　連結には、「 & 」スペース アンパサンド スペースを挟むこと、
　変数は、ダブルクオートで括らないこと
　「"」ダブルクオートが対になって過不足なく書けていること
　（一応、例示が例示なので、\の重複の有無や空白文字の過不足等にも注意）
などをよく確認しながら仕上げるようにしてください。
（追記：行末の「 _」や「, _」を「_」や「,_」に書換えてしまわないように）
（追記：「'」の位置を間違えたり消したりしてしまわないように）

以下、
こちらで実際にサンプルを用意して、フォルダ名以外は忠実な環境を用意して、
動作確認を済ませたものを編集して掲示します。

' ' 〓〓〓　列記しないで済む方法　「28」の部分を「YY」として変数に
' ' ｈttps://okwave.jp/qa/q9315863.html ｈttps://okwave.jp/qa/q9316809.html
Sub Re9315863w()
' ' // 必要な変数を宣言
Dim YY As String
Dim ary(1 To 12) As String
Dim i As Long
' ' // １．シート名の先頭を指定
　　YY = "28"　'　または　YY = 28
' ' // ２．[統合]機能のソースに渡す[統合元]配列を生成
' ' // ...１月(1)から12月(12)までの12シート分...の参照を配列に格納
　　For i = 1 To 12
　　　　ary(i) = "'C:\Users\\[給料.xlsm]" & YY & "(" & i & ")'!R2C1:R20C8"
　　Next i
' ' // ３．[統合]機能を実行（事前に統合先セルを選択しておくこと）
　　Selection.Consolidate _
　　　　Sources:=ary(), Function:=xlSum, _
　　　　TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub
' ' 〓〓〓

今後、ブック名も変数にしたい、という場合があれば、
...
　　XX = "給料.xlsm"
　　YY = "28"　'　または　YY = 28
...
　　For i = 1 To 12
　　　　ary(i) = "'C:\Users\\[" & XX & "]" & YY & "(" & i & ")'!R2C1:R20C8"
...
のような要領で。ややこしくなるけれど、
　"'C:\Users\\[" & XX & "]" & YY & "(" & i & ")'!R2C1:R20C8"
を
　"文字列値" & 変数 & "文字列値" & 変数 & "文字列値" & 変数 & "文字列値"
のようなイメージで分けて読むようにすると多少解り易いかな、と。

もし、シート名のフォーマットに不統一がある場合は、
列記したままの対応の方が易しいと思いますので、
その場合は以下のように、

' ' 〓〓〓　列記　「28」の部分を「YY」として変数に
Sub Re9315863w0()
Dim YY As String
' ' // １．シート名の先頭を指定
　　YY = "28"　'　または　YY = 28
' ' // ２．[統合]機能を実行（事前に統合先セルを選択しておくこと）
' ' // ...１月(1)から12月(12)までの12シート分...の[統合元]参照を個別に記載
　　Selection.Consolidate _
　　　　Sources:=Array( _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(1)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(2)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(3)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(4)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(5)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(6)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(7)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(8)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(9)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(10)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(11)'!R2C1:R20C8", _
　　　　　　"'C:\Users\\[給料.xlsm]" & YY & "(12)'!R2C1:R20C8"), _
　　　　Function:=xlSum, _
　　　　TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub
' ' 〓〓〓

「シートが見つからないというメッセージ」
というのが、２つめの質問タイトルでしたが、
こちらでは、添付画像のように、
｜統合元ファイル '[給料.xlsm]28(1)' を開くことができません。｜
というメッセージの不正実行しか確認できませんでした。
なので、「シートが見つからない」というトラブル
の原因を特定できた訳ではありません。
ただ、何れにしても、[統合元]への参照に不備がある
ということだろうと考えています。
再度、不正実行に終るようなことがあった場合は、
以下のマクロを実行して、[統合元]への参照内容を、
実際と比較して、ミスを見つけるよう試みて下さい。

'' ' 〓〓〓　[統合]機能の[統合元]に指定された内容を確認
' ' // 事前に統合先シートを選択しておくこと
Sub hoge()
Dim v
　　For Each v In ActiveSheet.ConsolidationSources
　　　　Debug.Print v
　　Next
End Sub
'' ' R2C1:R20C8は$A$2:$H$20のように表記されます。
'' ' 参照先のフォルダが現在のフォルダの場合はフォルダパスは省略されます。
'' ' 〓〓〓　実行後VBE画面からCtrl+Gでイミディエイトウィンドウを目視確認

以上になりますが、
もし、うまくいかない場合、現状をなるべくありのまま提示して
そちらで確認できたこと等を添えて再度、補足してみて下さい。
いよいよ煮詰まったりしたら、
再度、記録マクロを録り直したものから書き直すことで、
参照ミスを減らすことはできるのかもです。

お礼

懇切丁寧なご回答、誠にありがとうございます。

　何とか集計が出来ました。
　ダブルクオートを付けるべき箇所に無かったのが原因のようです。
　今後、お教え下さいました、列記しないでいい方法を勉強させて頂きます。

回答No.3

tsubu-yuki

こちらもまだ終わっていなかったのですね。
前の質問を閉じずに次の質問・・はマナー違反と言われるかもしれませんよ。

さて、本題。
　　　　 Range("B1:H20").FormulaR1C1 = "=SUM('C:\Users\\[給料.xlsm]28(1):28(12)'!RC)"
こんな感じで串刺し計算してやれば１行で良いかもしれません。
ただしこの場合、計算対象の一番左のシートが（１）、右端が（１２）と
並んでいる必要があります。
で、質問文中では「計算結果をどこに返したら良いか？」が判別できないので、
仮に「アクティブシートのＢ１：Ｈ２０」に返しています。

お礼

ご回答ありがとうございます。

試させて頂きましたが、従業員の増減で位置が変化するため、集計結果が違うように思われます。

回答No.2

Chiquilin

やり方そのものには疑問を感じますが そっちは置いときます。
適当な文字列で繋いだものを Splitで分割すればいいのでは？

For i = 1 To 12
myStr = myStr & "♪" & "'C:\Users\\[給料.xlsm]28(" & i & ")'!R2C1:R20C8"
Next i
myArry = Split(myStr, "♪")

お礼

ご回答ありがとうございます。
　お教え頂いた事を試してみました。
　myStrの値を見ましたら、それらしきものが見つかりました。
　もう少し頑張ってみます・
　ただ私自身、マクロで手順を記憶させて実行していただけですので、知識は皆無です。
　たまたま、マクロの編集で内容を見たとき例の列挙部分が気になり質問させて頂きました次第です。
　何か良い方法があれば御教え頂ければ幸いです。

回答No.1

Nouble

自信が、無い
の、ですが

Typeで
オブジェクトを、宣言する
とか、
複数箇所選択、指定を
使う、とか、
串刺し、指定を
使う、とか、
では
如何？

C:\Users\\[給料.xlsm]28(1):28(12)'!R2C1:R20C8
なのかな？