[[20060526112136]]　『ピボットをマクロの記録で・・』（まーちゃん）　ページの最後に飛ぶ

『ピボットをマクロの記録で・・』（まーちゃん）

 データシート
 納品日	客先名	発注No	工事番号	品名	金額	税込金額	検収日 支払日
4月13日	T	45-1	D0-03	ﾄﾞｰﾅﾂ   800,000	840,000	5月9日 5月20日
4月13日	T	45-2	D0-04	ﾄﾞｰﾅﾂ   650,000	682,500	5月9日 5月20日
4月6日	M	69-A02	M0-06A	シール	84,000	88,200	5月11日 5月31日
4月6日	M	69-A03	M0-06B	シール  156,000	163,800	5月11日 5月31日
4月15日	M	69-05	M0-08	シール	60,000	63,000
5月1日	T	45-4	D0-07	ﾄﾞｰﾅﾂ   600,000	630,000
5月5日	M	69-08	M0-10	シール	60,000	63,0004

 コピーシート
合計 / 金額						
客先名	納品日	工事番号	品名	検収日	支払日	合計
 M　　　　4/15　　　M0-08　　シール
 T　　　　5/1 　　　D0-07　　ﾄﾞｰﾅﾂ
 M        5/5       M0-10    シール

 こんにちは上記のデータシートから、マクロの記録でコピーシート（ピボットテーブ ル）を作成しています。
 データシートは仕事が決まるとデータが追加されていきます。

 ピボットは、納品されたものが、きちんと検収されたか、
 客先ごとに売上がいくらになるのかを、調べるために作成しています。

 ピボットを作成した時点では、うまくできていたのですが、
 データが追加されると、思うようにできなくて困っています。

 With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("検収日")
       .PivotItems("2005/12/13").Visible = False
        .PivotItems("2006/1/18").Visible = False
        .PivotItems("2006/2/2").Visible = False
        .PivotItems("2006/2/7").Visible = False
        .PivotItems("2006/2/13").Visible = False
        .PivotItems("2006/2/28").Visible = False
        .PivotItems("2006/3/2").Visible = False
        .PivotItems("2006/3/31").Visible = False
        .PivotItems("2006/4/4").Visible = False
        .PivotItems("2006/4/11").Visible = False
        .PivotItems("2006/4/17").Visible = False
        .PivotItems("2006/5/9").Visible = False
    End With

 上記の部分を”(空白）のみ表示”にしたいので、

　　　 .PivotItems("(空白)").Visible = True

 にしてみたのですが、うまくできませんでした。
 今の不具合としては、前回記録したときには　5/9　までしか入力していなかったので、データが追加された 5/11 も非表示にするため、

　　　　.PivotItems("2006/5/11").Visible = False

 をコードに記述しないといけないことです。
 毎回データが追加されるたびに手入力をしていたのでは、マクロの意味がないような・・・
　
 とりあえず、コードを記述しますので、なにか良い方法をご教授いただければと思います。
 わかりにくい説明だと思いますが、どうかよろしくお願いします。

 Sub 集計だよ()

    ActiveWorkbook.PivotCaches.Add _
    (SourceType:=xlDatabase, SourceData:="データ!R2C1:R65536C9") _
    .CreatePivotTable TableDestination:="", _
    TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2", _
    DefaultVersion:=xlPivotTableVersion10

    With ActiveSheet
         .PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
         .Cells(3, 1).Select
         .PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("納品日").Subtotals = _
         Array(False, True, False, False, False, False, False, False, False, False, False, False)
         .PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("客先名").Subtotals = _
         Array(False, True, False, False, False, False, False, False, False, False, False, False)
         .PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("工事番号").Subtotals = _
         Array(False, False, False, False, False, False, False, False, False, False, False, False)
         .PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("品名").Subtotals = _
         Array(False, False, False, False, False, False, False, False, False, False, False, False)
         .PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("検収日").Subtotals = _
         Array(False, False, False, False, False, False, False, False, False, False, False, False)
         .PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("支払日").Subtotals = _
         Array(False, False, False, False, False, False, False, False, False, False, False, False)
         .PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").AddFields RowFields:= _
         Array("客先名", "納品日", "工事番号", "品名", "検収日", "支払日")
    End With

    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("金額")
        .Orientation = xlDataField
        .Caption = "合計 / 金額"
        .Function = xlSum
    End With

    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("納品日")
        .PivotItems("(空白)").Visible = False
    End With

    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("検収日")
        .PivotItems("2005/12/13").Visible = False
        .PivotItems("2006/1/18").Visible = False
        .PivotItems("2006/2/2").Visible = False
        .PivotItems("2006/2/7").Visible = False
        .PivotItems("2006/2/13").Visible = False
        .PivotItems("2006/2/28").Visible = False
        .PivotItems("2006/3/2").Visible = False
        .PivotItems("2006/3/31").Visible = False
        .PivotItems("2006/4/4").Visible = False
        .PivotItems("2006/4/11").Visible = False
        .PivotItems("2006/4/17").Visible = False
        .PivotItems("2006/5/9").Visible = False
        .PivotItems("2006/5/11").Visible = False
    End With

    Range("A:C,E:G").Select
    Selection.ColumnWidth = 12
    Columns("D:D").Select
    Selection.ColumnWidth = 40
    Columns("G:G").Select
    Selection.Style = "Comma [0]"

    Sheets("コピー").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Sheets("Sheet1").Select
    Cells.Select
    Selection.Copy
    Sheets("コピー").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select

        Application.DisplayAlerts = False
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True

  End Sub

二行目の

 (SourceType:=xlDatabase, SourceData:="データ!R2C1:R65536C9") _

この、R2C1:R65536C9　を範囲を可変にしてやればよいのでわ。

 ↑の方へ
 ご回答ありがとうございました。
 でも、マクロ超初心者なもので、可変がよくわかりません。
 できれば、詳しくご説明いただけないでしょうか。
 （まーちゃん）

 範囲は充分大きい範囲であるし、変更する必要もないように思えます。
この場合、検収日フィールドの"(空白)"アイテム以外を非表示にするのだから、
アイテムをループしてアイテム名をチェックしながら表示非表示を分岐する方法が良いでしょう。
With～End Withステートメントの部分を次のように変更してみてください。
　
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("検収日")
        Dim PvItem As PivotItem

        For Each PvItem In .PivotItems
            If PvItem.Name <> "(空白)" Then
                .PivotItems(PvItem.Name).Visible = False
            End If
        Next PvItem
    End With
　
(みやほりん)(-_∂)b

 みやほりんさま、思い通りにうまく動作してくれました。
 ありがとうございます。m(__)m

 以前に教えていただいた別のマクロを修正して（修正の方法も学校でお聞きし)、
 使用していたのですが、少し思っている動作と違うものになってきてました。
 そこで、いろいろ思案して、ピボットが一番考えに近いものができるので、
 ピボットを使い、マクロの記録をしようと、がんばってマクロを記録し、
 できたー！！と思っていたのですが、またつまづき・・・、
 ピボットのマクロはみなさんあまり使用していないのか、
 私の探し方が悪いのか、過去ログ・全文検索をしても、他のサイトで探しても
 うまく探すことができず、悩んでおりました。
 この学校では、みやほりん様と縁があるのか？（私なんかと縁があっても迷惑かな？）
 マクロの作成では、いつもいつもお世話になっています。m(__)m
  （全然成長できず、はずかしくて名前を変えていました。）
 なかなか成長しない私ですが、みなさん今後とも宜しくお願いいたします。m(__)m

 （まーちゃん）（まりお）でした

 ＞全然成長できず、はずかしくて名前を変えていました。
恥ずかしがることはないですよ。
　
回答者は相談された方のハンドルネーム（HN）をおぼろげにでも覚えていたりします。
相談の流れをつかむためにHNを検索することも多いです。
HN変更は、過去からの流れに沿った回答を阻害することがあるので、
統一しておくことをお勧めします。
(みやほりん)(-_∂)b

 ありがとうございます。
 これからは、ハンドルネームを変えずに相談するようにします。
 一歩でも、1ミリでも、みなさんに近づけるようにがんばります。
 これからも困った時は相談させていただきますので、宜しくお願いします。
 （まりお）

 みやほりんさん、まーちゃんさん

 はじめまして。
 ピボットテーブルの可変化（PivotItemの可変）、
 以下とても参考になりました。
 ありがとうございました。

    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ2").PivotFields("検収日")
        Dim PvItem As PivotItem
        For Each PvItem In .PivotItems
            If PvItem.Name <> "(空白)" Then
                .PivotItems(PvItem.Name).Visible = False
            End If
        Next PvItem
    End With

 （まさきち）

コメント返信：

[ 一覧(最新更新順) ]

[[20060526112136]] 『ピボットをマクロの記録で・・』（まーちゃん） ページの最後に飛ぶ

[[20060526112136]]　『ピボットをマクロの記録で・・』（まーちゃん）　ページの最後に飛ぶ