[[20170329190801]]　『マクロ記録の整理』（ｋｓ）　ページの最後に飛ぶ

『マクロ記録の整理』（ｋｓ）

ピボットテーブルを使いマクロ記録してみたのですが
最下行（不特定）の設定と内容の整理したいのですが
お解りの方アドバイスお願いします。
Sub Macro4()

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "集計A4横!R3C2:R7C8", Version:=6).CreatePivotTable TableDestination:= _
        "集計A4横!R4C15", TableName:="ピボットテーブル8", DefaultVersion:=6
    Sheets("集計A4横").Select
    Cells(4, 15).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("ピボットテーブル8").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル8").PivotFields("グループ"), "合計 / グループ", xlSum
    ActiveSheet.PivotTables("ピボットテーブル8").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル8").PivotFields("定価（単価）"), "合計 / 定価（単価）", xlSum
    ActiveSheet.PivotTables("ピボットテーブル8").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル8").PivotFields("定価（合計）"), "合計 / 定価（合計）", xlSum
    ActiveSheet.PivotTables("ピボットテーブル8").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル8").PivotFields("金額"), "合計 / 金額", xlSum
    ActiveSheet.PivotTables("ピボットテーブル8").PivotFields("合計 / 定価（単価）").Orientation = _
        xlHidden
    With ActiveSheet.PivotTables("ピボットテーブル8").PivotFields("グループ")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ピボットテーブル8").PivotFields("グループ")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ピボットテーブル8").PivotFields("グループ")
        .Orientation = xlColumnField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("ピボットテーブル8").PivotFields("グループ")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ピボットテーブル8").PivotFields("グループ").Orientation = xlHidden
    With ActiveSheet.PivotTables("ピボットテーブル8").PivotFields("グループ")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ピボットテーブル8").DataPivotField.Orientation = xlHidden
    ActiveSheet.PivotTables("ピボットテーブル8").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル8").PivotFields("金額"), "合計 / 金額", xlSum
    ActiveSheet.PivotTables("ピボットテーブル8").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル8").PivotFields("定価（合計）"), "合計 / 定価（合計）", xlSum
    Range("P5:Q8").Select
    Selection.Style = "Comma [0]"
    Range("Q12").Select
End Sub

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

＞最下行（不特定）の設定と内容の整理したいのですが
ピボットテーブルの対象範囲を自動で設定したいということですか？
それなら、どういう範囲にしたいか説明してください。
こちらからは、貴方の頭の中が見えないので。
　
内容の整理と言っても、何が必要なものかはわかりません。
　
コーディングの簡略化のことなら例えば、

    Dim pvt As PivotTable
    Set pvt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "集計A4横!R3C2:R7C8", Version:=6).CreatePivotTable(TableDestination:= _
        "集計A4横!R4C15", TableName:="ピボットテーブル8", DefaultVersion:=6)
　
などとして、
変数 pvtで書き換えるとスッキリする、くらいでしょうか。

（γ） 2017/03/29(水) 21:04

（γ）さん
ありがとうございます。
"集計A4横!R3C2:R7C8"この部分Ｒ７Ｃ８ですが
Ｈ列の最下行まで指定する文法はどのように表現したら
よいのでしょうか？

よろしくお願いします。
帳票は以下の通りです
３行目見出し行
A列は空きです

  A    B     C     D・・F    G     H
3
4　　　B4ここからデータ入力
5
6
但しF列　H列は数式が入っています。
B列入力済みで最下行としそこから　H列までをデータ取得範囲とする。
以上説明が下手かもしれませんが概要です。
（ｋｓ） 2017/03/30(木) 01:28

SourceData引数にはRangeオブジェクトを与えれば良いので、
別にR1C1形式でないといけないわけではありません。
お決まりのEndプロパティを使った書き方でOKです。
例えば、

    Set rng = Range("B3", Cells(Rows.Count, "H").End(xlUp))
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= rng, _
         以下省略
など。
Sheetを複数使っているなら指定しないといけませんが、
Activesheetだけなら指定する必要もないと思います。

（γ） 2017/03/30(木) 07:09

（γ）さん
ありがとうございます。
ちょっと質問ですが
＞Set rng = Range("B3", Cells(Rows.Count, "H").End(xlUp))
上記で得られる範囲は、記で試したところ　$B$3:$h$330です

＞但しF列　H列は数式が入っています。
330行目までしたフィルで数式です。ピボットテーブルの場合
この範囲（空白）は関係ないのでしょうか？今回のケースの場合
B列を含むデータ記入済み行は7行目までです。
ちなみに7行目まで範囲の指定（数式抜き）方法はありますか？
よろしくお願いします。

 Sub test2()
 Dim Rng As Range
 Set Rng = Range("B3", Cells(Rows.Count, "H").End(xlUp))
 MsgBox Rng.Address
 End Sub
（ｋｓ） 2017/03/30(木) 12:33

最終行をB列で求めれることはできないのでしょうか？

（マナ） 2017/03/30(木) 23:36

（マナ）さん
アドバイスありがとうございます。
＞最終行をB列で求めれること
Offsetを使うのですか？

（ｋｓ） 2017/03/31(金) 00:47

H列には予め数式が入っていて、B列はそうではなくデータのみが入っているのであれば、
Set rng = Range("B3", "H" & Cells(Rows.Count, "B").End(xlUp).Row)
とか
Set rng = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Resize(,7)
とかでやってみてください。

（γ） 2017/03/31(金) 07:10

（γ）さん
Resizeですか
ありがとうございました。

（ｋｓ） 2017/03/31(金) 09:04

コメント返信：

[ 一覧(最新更新順) ]

[[20170329190801]] 『マクロ記録の整理』（ｋｓ） ページの最後に飛ぶ

[[20170329190801]]　『マクロ記録の整理』（ｋｓ）　ページの最後に飛ぶ