色付きセルの合計を自動更新したい

シートA：すべてのデータがあり、構成として顧客コード、顧客名、商品名、金額、、、等がある。
シートB～：顧客ごとのシート

①シートAで、ある条件に該当するものの行に色を付ける。
②シートAのデータを、各顧客ごとに顧客シートに行単位で振り分ける。
③顧客シートの中で、金額を合計するが、あらかじめ1行目にデータがコピーされたら、自動的に合計されるようにおく。
金額の合計で、シート内の金額の合計を、色付き行（セル）に関係なく合計するものと、色のついた行（セル）だけ合計するものと2つ集計したい。

色に関係なく合計する数式は問題ないが、色のついた行（セル）だけを、データをシートAからコピーした時点で自動的に合計するようにしたいと思いますが、同じような内容を検索しても、自動更新はしないと書かれているようです。ColorSumなどです。
どなたかコピーと同時に合計されるようにする方法をご存知ありませんでしょうか。ぜひ、お知恵を拝借願います。

No.4 ベストアンサー 回答者： WindFaller 回答日時： 2019/09/22 16:49

おっしゃるように、イベント・ドリブン型マクロというのは、基本的には手動で、「呼び出し」が発生するもので、マクロのコピーには発生しません。

No.3のコードは、手作業を想定したものです。

コピー＆ペーストするなら、コピー＆ペーストそのものに計算機能を設けたほうが早いですね。
例えば、このようなコードです。ほかにも、SendKey を使う方法があるかと思いますが、こちらのほうがキレイ(個人的感覚)ではないかと思います。
ThisWorkbook に取り付けた、Workbook_SheetSelectionChangeは不要ですから、取り除いてください。

'//標準モジュール
Sub CopyPasteMacro()
Dim Sheet_A As Worksheet
Dim Sheet_B As Worksheet

Set Sheet_A = Workbooks("Book_A.xlsm").Worksheets("Sheet1")
Set Sheet_B = Workbooks("Book_B.xlsm").Worksheets("Sheet1")

With Sheet_A
　 'コピー元のブックが、C1からだと仮定した場合
　 .Range("C1", .Cells(Rows.Count, "C").End(xlUp)).Copy Sheet_B.Range("D1")
End With
With Sheet_B
　'Book_Bにユーザー定義関数がある場合(ユーザー定期関数の再設定)
　 .Range("D1").DirectDependents.FormulaLocal = .Range("D1").DirectDependents.FormulaLocal
End With
End Sub

この回答へのお礼

ありがとうございます。
お示しいただきました内容を理解できたように思いますので、これを参考に設定いたします。

お礼日時：2019/09/22 18:48

No.3 回答者： WindFaller 回答日時： 2019/09/21 23:17

>CSUM(D:D) ←D:Dとしたのは、毎月同じシートを使うので、

VBAでは、範囲を1列にしないほうが良いと思うのですが……。

>データをシートAからコピーした時点で自動的に合計するようにしたいと思いますが
ふつうは、F9で再計算させればよいのですし、引数を再入力すれば、値は変えられますが、自動でするなら、

'//Thisworkbook モジュールに以下のようにおけばよいです。

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Calculate
End Sub
------------------------
よく関数の中に、Application.Volatileを使う人がいますが、あまり意味がありません。

この回答へのお礼

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

お示しいただきましたように、ThisWorkbookモジュールにコードを入力しました。

私の勘違いなのかわかりませんが、再度色を付けたデータを該当シートにコピーしましたが、＃NAME?となります。
説明不足だったかもしれませんが、このコピーは手動ではなく、マクロを使ってコマンドボタンでコピーしております。
ちなみに、手動でコピーしましたら、確かに自動で計算されます。
私が今設定しるように、マクロを使ってのコピーの場合、自動計算させるのは無理でしょうか。
ご面倒なお尋ねで申し訳ございません。

お礼日時：2019/09/22 13:51

No.2 回答者： 銀鱗 回答日時： 2019/09/21 03:06

カテゴリが「Excel」で、且つアプリケーション名が省略されていますので、Microsoft Excel 以外のスプレッドシートは考えない事にします。

「色を付ける条件」をSUMIF関数の合計条件に使ってはいかがですか？

この回答へのお礼

ありがとうございます。

今の設定は、
コードを
Function CSUM(xy As Range) As Double
Dim s As Double
Dim myrange As Range
For Each myrange In xy
'指定の色で塗られたセルだけ選んで数値を足します
If myrange.Interior.ColorIndex = 3 Then
s = s + myrange.Value
End If
Next
CSUM = s
End Function
を使い、
合計値のセルに
＝CSUM(D:D)　←D:Dとしたのは、毎月同じシートを使うので、前のデータを削除して新しいデータをコピーするためです。
を入力しています。
このままだと、自動では更新せず、CTRL+ALT+F9などで更新させてあげなければなりません。
これを、コピーされると同時に計算結果が表示されるためには、
各シートモジュールに
Private Sub Worksheet_Change(ByVal Target As Range)
から始まるコードを入れたら良いのではないかと考えましたが、いかがでしょうか。
もし、この考え方が正しければ、具体的にどのようなコードにすればよいかご教授いただけますでしょうか。

お礼日時：2019/09/21 11:49

No.1 回答者： fujillin 回答日時： 2019/09/21 00:53

こんばんは

ご存知のようにセルの色の判定は関数ではできませんので、ご質問の内容を実現するには、いずれにしろVBAを利用することになると考えられます。

想像するところ、セルの色だけ変わることはなく値も同時に変わるのでしょうから、ご質問の合計値を算出するユーザ定義関数を作成しておいて、これを通常の関数と同様に集計したいセルに設定しておけば、対象とするセル範囲の値が変われば自動更新する仕組みにすることは可能です。

とは言え、ユーザ定義関数を作成するのには、VBAの智識が不可欠ではありますが。