エクセルのマクロ・ＶＢＡを駆使して複合グラフを作成する

Excel2013にて個人毎に項目Ａ（金額）、Ｂ（金額）、Ｃ（率：パーセント表示）を列Ａに個人名、列Ｂに項目Ａ、Ｂ、Ｃを入力して、１行目に４月から３月までの表を作成し、個人名で特定できる様オートフィルターをかけて複合グラフを作成しています。グラフはＡ、Ｂを主軸、Ｃを第２軸としていますが、Ａ、Ｂの数値に対して、Ｃは余りに小さいため、一旦、３項目を棒グラフにて作成した後、主軸を対数グラフにしてＣの棒グラフが可視できるようにした上でＣの棒グラフを選択し、折れ線グラフに変更して、主軸を元に戻しています。
ざっくり言うとこの手順で複合グラフを作成していますが、個人名に対して３項目楯列にデータが存在するため個人名部分のセルは結合しているいます。そのため通常のフィルターでは思うように機能しないため下記ＵＲＬの裏技を使用してフィルターをかけています。
http://officetanaka.net/excel/function/tips/tips …

これをマクロとして
１．オートフィルターを設定し、コンボボックスにて個人名を指定してフィルターをかける。
２．絞り込んだ表を参照範囲として指定して、上記のような複合グラフを作成する。
３．表をグラフを印刷する。
４．グラフを削除し、オートフィルターを解除する。
の順に登録し、にわかなＶＢＡの知識で加工しています。
この方法で２人目まえではうまくグラフ表示されるのですが３人目から同じ構文で参照範囲だけ変更したマクロを呼び出しグラフを作成するという手順をとっているにも関わらずうまく複合グラフが作成できません。
※ＶＢＡ初心者のため、個人毎にフィルターをかけたあと個々に複合グラフを作成するマクロを作って呼び出しています・・・

やっていることに無理があるのか、もっと良い方法があるのか分かり兼ねています。よかったらアドバイスをお願いします。
ちなみにグラフを作成しているVBAの構文を以下の通りです。

Sub グラフの作成01()
'
' グラフの作成01 Macro

'
Range("B1:N4").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("'2016'!$B$1:$N$4")
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).AxisGroup = 2
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).ScaleType = xlLinear


'グラフの移動
Dim GName As String
GName = Mid(ActiveChart.Name, Len(ActiveSheet.Name) + 2)
With ActiveSheet.Shapes(GName)
.Left = 100
.Top = 150
.Width = 1000
End With


End Sub


これが１人目の複合グラフを作成するマクロで２人目以降は参照範囲を変更しています。

No.1 ベストアンサー 回答者： moto_koukousei 回答日時： 2016/07/30 00:22

１）オートフィルター機能を使わない方法です。

２）（氏名を選んだら）その人物のデータを別シートにVBAで転記してしまうことにして、
　　その別シートの該当範囲をソースデータにして、必要なグラフを手動でつくっておくと、
　　（氏名を選んで）その人物のデータを別シートにVBAで転記したら、
　　グラフに自動反映されると思います。　
　　その別シートを印刷すれば、データとグラフが印刷できます。
　　毎回グラフをＶＢＡで作る必要はないと思います。
　　グラフを削除する操作も無用だと思います。
　　別シートには、該当範囲のデータと連動するグラフを手動でつくっておくので、
　　かなり複雑な操作を要するグラフでも、VBAの面倒くささを気にしなくて済み楽です。
　　
３）２）の方法が一番簡単だと思いますが、データは関係なく、複合グラフだけならば
　　次のようなVBAでも、グラフを作ることは出来ます。
①　B1にグラフ化する個人名を選ぶ入力規制を作っておく。
　　　Sub Macro1()
'　　　　《名前を、'　　B1で選択できるリストにセットし直す》
Dim r As Range: Dim nl
For Each r In Range("A2:A1000")
If r.Value <> "" Then nl = nl & "," & r.Value
Next r
With Range("B1").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=nl
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
End With
End Sub
② B1に表示されている名前の人のデータを元に、
　　新たにグラフを作成する。
Sub Macro2()
' B1のセルに表示されている個人名の12ヶ月データを元にして
' 主軸対数メモリの棒グラフ(A金額、B金額)を
' 副軸最高値130の折れ線グラフ（C率）を作成し
' グラフのタイトルに、B1のセルに表示されている文字列を書く
Dim rr
rr = WorksheetFunction.Match(Range("B1"), Range("A1:A10000"), 0)
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
.SetSourceData (ActiveSheet.Range( _
"$B$1:$N$1,$B$" & rr & ":$N$" & rr + 2)), xlRows
.ChartType = xlColumnClustered
.Axes(xlValue).ScaleType = xlLogarithmic
.SeriesCollection(3).AxisGroup = 2
.SeriesCollection(3).ChartType = xlLineMarkers
.Axes(xlValue, xlSecondary).MaximumScale = 130
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = Range("B1")
End With
ActiveSheet.Activate
Range("B1").Select
Application.ScreenUpdating = True
End Sub

この回答へのお礼

moto_koukousei　様

丁寧な回答ありがとうございました。
別シート作成した方が簡単かな？と思っていたのですが、やはりそのやり方が簡単なようですね。
試してみようと思います。
ありがとうございました。

お礼日時：2016/08/01 09:06

No.2 回答者： hallo-2007 回答日時： 2016/07/30 13:25

私も発想を変えて、グラフを自動で作成するマクロではなく

必要なデータを抽出するマクロや関数を考えて方が良いと思います。
別途、一枚シートを準備して
名前を入れると（選択すると）グラフに必要なデータが抽出されるようにする。
複合グラフを配置しておく。

名前を変えるだけで、必要なグラフに変わると思うのですが、如何でしょうか。
一例ですが
これをマクロとして
１．別シートにコンボボックスにて個人名を指定してフィルターオプションでデータを抽出する。
２．そのシートには上記のような複合グラフが準備してある。
３．表をグラフを印刷する。
４．グラフを削除し、オートフィルターを解除する必要もありません。

この回答へのお礼

hallo-2007　様

回答ありがとうございます。
moto_koukouseiさんの回答と同様、やはり別シートを作りコピーの方が簡単のようですね。
今から試してみようと思います。
ありがとうございました。

お礼日時：2016/08/01 09:08