[[20041116165844]]　『データーの統合マクロのシート名に変数を利用した』（kuzu）　ページの最後に飛ぶ

『データーの統合マクロのシート名に変数を利用したい』（kuzu）

マクロを初めて１ヶ月の初心者です。
データーの統合マクロのシート名に変数を利用したいのですが、
よく、解りません。教えて下さい。よろしくお願いします。

  Ｂｏｏｋに合計シートと複数の売上シートがあり、      
合計シートに各売上シートを統合集計する場合、
例えば、４シートをマクロ記録で統合すると
合計シートをアクティブにしておき、
    Range("C7:F68").Select
    Selection.ClearContents
    Range("C7").Select
    Selection.Consolidate Sources:=Array( _
         "'東京売'!R10C10:R200C13" _
        ,"'大阪売'!R10C10:R200C13" _
        ,"'京都売'!R10C10:R200C13" _
        ,"'福岡売'!R10C10:R200C13" _
        ), Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
でスムーズに動作するのですが、本当は１０シート以上で、シート名も変更する
可能性もあり、合計シートM列にシート名一覧表を作成して、一覧表を変更する
とシート名も自動的に変わる用にリンクさせていますので、
　　　　　ＴｈｉｓＳｈｅｅｔ　=　Ｒａｎｇｅ（”M”&　i+1）
　　　　　"'ThisSheet'!R10C10:R200C13"
と変数を利用してマクロ行を減らしたいのですが。
　よろしくおねがいします。

 一応自分なりのやり方でやってみた。
配列を変数に格納する部分と配列から統合を
実行する部分に絞ってあるので、セル範囲のクリア
なんかは肉付けしてください。
　
 Sub 配列変数()
 Rem TEST_20041116
 Dim MyAry() As Variant
 Dim i As Long, MyRow As Long
 Rem M列、合計シート名
 Const MyListCol As Long = 13 'M列
 Const MyWsh As String = "Sheet1" '合計シート名

 Rem 変数MyRow（M列最下行）
     MyRow = Sheets(MyWsh).Cells(65536, MyListCol).End(xlUp).Row
 Rem 変数MyAryにシート名&セル範囲の配列格納
     ReDim MyAry(MyRow - 1)
     For i = 1 To MyRow
         MyAry(i - 1) = Sheets(MyWsh).Cells(i, MyListCol).Value & "!R10C10:R200C13"
     Next i

 Rem 変数MyAryのセル範囲を統合
     Sheets(MyWsh).Range("C7").Consolidate _
         Sources:=Array(MyAry()), _
         Function:=xlSum, TopRow:=False, _
         LeftColumn:=True, _
         CreateLinks:=False
 End Sub
　
（みやほりん）
#シート名リストはM1から入力されていることを想定。
#試しちゃってたら「書き忘れごめんなさい」

（kuzu）

早速のご指導ありがとうございました。
何分初心者ですので１行１行の解読に時間がかかりそうです。
全文を理解したうえでトライしてみようと思います。
取り急ぎ、お礼申し上げます。

もう一つ教えて頂きたいのですが、
ある一つの売上データーシートに、ある条件でデーターを抽出索出力して、合計シート
に統合集計する場合に、変数を利用して、

　　　　ThisSheet＝Range("M"＆i+1)

     Sheets(ThisSheet).Range("A3:H65000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(ThisSheet).Range( _
         "o7:p8"), CopyToRange:=Sheets(ThisSheet).Range("o10:s65000"), Unique:=False
     Selection.Consolidate Sources:= _
        "'ThisSheet'!R11C15:R65000C19" _
       , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

とすると、フィルターは動作しますが、データー統合のマクロが「統合元のファイルは開けません。」と表示されてしまいます。
　　　　「"'ThisSheet'!R11C15:R65000C19"」
の部分を「 ''」を削除したり、「&」　を挿入してみたりしましたが、うまく動作しません。
なんとか良い方法はないでしょうか。
よろしくお願いします。

 文字列の作り方の問題です。もとの引数はは次のように
 "'東京売'!R10C10:R200C13"
ダブルクォーテーションで囲んだ文字列となります。
だから、ダブルクォーテーションの中にある限りは
"'ThisSheet'!R11C15:R65000C19"
これは変数としてではなく、"'ThisSheet'"というシート名と受け取られます。
　
ThisSheetを変数として文字列を作るには
"'" & ThisSheet & "'!R11C15:R65000C19"
このように＆で結合するにしてもダブルクォーテーションの外に
出してやるのがセオリーです。
（みやほりん）

(kuzu)

  "みやほりん"さん有り難う御座います。
　２番目の質問は、「＆」の前後にスペースが必要なことに気づくが遅く手間
　どりましたが無事解決しました。ただ、一番目の解答は勉強不足で今少し
　時間が必要です。

(KUZU)

  やっとできました。”みやほりん”さん、ありがとうございました。
　実情は、例よりもちょっと複雑でしたが、なんとか動作しました。
　何日間のもやもやがすっきりです。
　これからもよろしくお願いします。

コメント返信：

[ 一覧(最新更新順) ]

[[20041116165844]] 『データーの統合マクロのシート名に変数を利用した』（kuzu） ページの最後に飛ぶ

[[20041116165844]]　『データーの統合マクロのシート名に変数を利用した』（kuzu）　ページの最後に飛ぶ