人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

ピポットテーブルウィーザードを用いてピポットテーブル作成時にマクロの自動記録を実行し以下が記録されました。データの範囲を列方向と行方向のデータの終りまでに設定するにはどのように書きなおせばいいかご教示いただければ幸いです。

Sub ピポットテーブル作成()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"main!R1C1:R6C7").CreatePivotTable TableDestination:="", TableName:= _
"ピボットテーブル6", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("ピボットテーブル6").PivotFields("仕入先")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル6").PivotFields("支払月")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル6").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル6").PivotFields("支払い額"), "合計 / 支払い額", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

●質問者: nmfo4n67
●カテゴリ:コンピュータ
✍キーワード:ADD SELECT sub ウィーザー データ
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● きゃづみぃ
●27ポイント

>データの範囲を列方向と行方向のデータの終りまでに設定するには

この内容が 理解しかねます。

マクロの記録が出来るのでしたら、

データの範囲を列方向と行方向のデータの終りまでに設定

してから 再度 マクロの記録を とってみたらいかがでしょうか?


2 ● うぃんど
●27ポイント

【1】変更箇所(アンダーバーで繋がっているので3行ではありますが1行という扱いになります)

 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
 "main!R1C1:R6C7").CreatePivotTable TableDestination:="", TableName:= _
 "ピボットテーブル6", DefaultVersion:=xlPivotTableVersion10

【2】変更後

 Dim lasrRow As Integer
 lastRow = Range("a65536").End(xlUp).Row

 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
 "main!R1C1:R6C" & i).CreatePivotTable TableDestination:="", TableName:= _
 "ピボットテーブル6", DefaultVersion:=xlPivotTableVersion10

【3】説明

RはRow(行)、CはColumn(列)を意味しますのでR1C1:R6C7はA1:G7という範囲指定と同じ意味になります

マクロを作成した時の最終行が7となっていますので、7の部分を可変に変えました


3 ● ycyc
●26ポイント ベストアンサー

●「データの終りまで」というのが曲者で、excelにはデータの終わりを取得する方法がいくつかあります。


「A列に入力されている最後の行」を取得するのがwindofjulyさんの方法です。

【参考】

http://www.niji.or.jp/home/toru/notes/8.html で記述されている「書式付きセルを除外する」方法でサンプルを書いてみました。

●もうひとつ引っかかるのが、pivotテーブルを何回も書いていると、テーブル名が重複して、エラーになるケースがあります。ので、テーブル名を"ピボットテーブル1"として、存在すれば、削除するようにしました。

●割とよくありがちなVBAの失敗として、Selectionとか、ActiveBook,ActieSheetとかを使うと、デバッグしているときにSelectionが変わってしまって、わけがわからない動きになることがあります。ので、Active~を最小にしました。

Sub ピポットテーブル作成()
 Dim book As Workbook
 Set book = ActiveWorkbook
 Dim dataSheet As Worksheet
 Dim pivotSheet As Worksheet
 Set dataSheet = book.Worksheets("main") ' データシート名はmainです
 Set pivotSheet = book.Worksheets("pivot") ' ピボットテーブルを作るシートはpivotです
 
  '最大行、最大列を求める
 Dim maxRow As Long
 Dim maxCol As Long
 With dataSheet.UsedRange
 maxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).row
 maxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
 End With
 Dim area As Range
 Set area = dataSheet.Range( _
 dataSheet.Cells(1, 1), _
 dataSheet.Cells(maxRow, maxCol))
 
  ' ピボットテーブル名
 Dim pivotName As String
 pivotName = "ピボットテーブル1"
 Call removePivot(pivotSheet, pivotName)
 
 book.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=area) _
 .CreatePivotTable _
 TableDestination:=pivotSheet.Range("A3"), _
 TableName:=pivotName, _
 DefaultVersion:=xlPivotTableVersion10
 book.ShowPivotTableFieldList = True
 With pivotSheet.PivotTables(pivotName).PivotFields("仕入先")
 .Orientation = xlRowField
 .Position = 1
 End With
 With pivotSheet.PivotTables(pivotName).PivotFields("支払月")
 .Orientation = xlColumnField
 .Position = 1
 End With
 pivotSheet.PivotTables(pivotName).AddDataField _
 pivotSheet.PivotTables(pivotName).PivotFields("支払い額"), "合計 / 支払い額", xlSum
 book.ShowPivotTableFieldList = False
End Sub
' 既存のピボットテーブルを削除
Private Sub removePivot(pivotSheet As Worksheet, pivotName)
 On Error GoTo OnError
 pivotSheet.PivotTables(pivotName).PivotSelect "", xlDataAndLabel, True
 Selection.ClearContents
OnError:
  ' 存在しなければエラーになります
End Sub


4 ● simoke123
●10ポイント (はてなにより削除しました)
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ