Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2013)
ピボットテーブルの行の2列目の1つのアイテムをフィールドの一番下に並べ替えたい
投稿日時: 20/03/09 16:26:48
投稿者: RyuRyu

ピボットテーブルの行の2列目のフィールド内にある1つのアイテムをフィールドの一番下にドラッグするというプロセスをマクロで行いたいのですが、なかなかうまくいきません。
イメージは
      列1
行1  行2  
集計1 ***  ×
    AAA ××
     BBB  〇
  111 ×
       222  ×
集計2 111  ×
    222  ×
   AAA  ××
   BBB  〇
 
AAAを集計1、集計2の一番下にくるようにドラッグします
      列1
行1  行2  
集計1 ***  ×
     BBB  〇
  111 ×
       222  ×
    AAA ××
集計2 111  ×
    222  ×
   BBB  〇
   AAA  ××
以下のコードは動くのですが、PivotItemは並べ替えとはなりません。
AAAをフィールドの一番下に並べ替えるにはどうすればいいのでしょうか?
教えてください。
よろしくお願いします。
 
  Dim BLong as Long, AAAItemNum as Long
  BLong = Sheets("Sheet1").Range("B5").End(xlDown).Row
     
    Dim pvtName1 As PivotTable
    ptblName1 = "ピボットテーブル1"
        
    FindAAA = Sheets("Sheet1").Range("B5:B" & BLong).Find("AAA").Row
 
 
  Sheets("Sheet1").Range("B" & FindAAA).Activate
    MsgBox "The active item is in position number " & _
    ActiveCell.PivotItem.Position
    AAAItemNum = ActiveCell.PivotItem.Position  
 
 With Sheets("Sheet1").PivotTables(ptblName1).PivotFields("集計1")
        .AutoSort xlManual, "集計1" '詳細>並べ替え>手動
        .PivotItems("AAA").Position = AAAItemNum
    End With
 
    

回答
投稿日時: 20/03/09 16:54:03
投稿者: simple

モジュールの冒頭に Option Explicit と入れて、
未宣言の変数が無いようにしたものを再アップできませんか?
変数間違い(が仮にあったとして)という雑音は、最初から排除しておきたいのです。
 
また、元データのサンプル(ごく簡単なもので結構)を提示できませんか?(無理にとは言いませんが。)
Positionプロパティが無効という事象の検証したいのでしょうから、
質問者と回答者が、お互い同じデータで議論した方が有益だと思いますので。

投稿日時: 20/03/09 17:55:21
投稿者: RyuRyu

simpleさん
早速ご覧くださりありがとうございます!
アップしたコードがいろいろ雑音が入っておりました。大変失礼いたしました。
実際のデータをお渡しすることはできませんが、今回の検証用に用意したデータが以下になります。
 
コード    AAA    Type    Shippt    weight
XL    2020/3/13    TypeB    111    1.05
XL    2020/3/12    TypeB    111    5.45
XL    2020/3/17    TypeB    111    6.9
XL    2020/4/16    TypeB    111    6.9
XL    2020/3/17    TypeB    111    9.75
XL    2020/3/13    TypeB    111    0.65
XL    2020/3/13    TypeB    111    0.65
XL    2020/3/13    TypeB    111    0.65
XL    2020/3/11    TypeB    111    0.25
XL    2020/4/15    TypeB    111    1.35
XL    2020/4/16    TypeB    111    0.45
XL    2020/4/16    TypeB    111    0.45
XL    2020/3/19    TypeB    111    6.1
XL    2020/3/19    TypeB    111    1.15
XL    2020/3/19    TypeB    111    3.95
XL    2020/3/12    TypeB    111    4.8
XL    2020/3/12    TypeB    111    11.85
XL    2020/4/16    TypeB    111    0.1
XL    2020/3/11    TypeB    111    1.95
XL    2020/3/11    TypeB    111    1.1
XL    搬入済み    TypeB    111    0.05
XL    搬入済み    TypeB    111    0.3
XL    搬入済み    TypeA    333    19.65
XL    搬入済み    TypeB    111    0.4
XL    搬入済み    TypeA    111    20
XL    搬入済み    TypeA    111    11.4
XP    搬入済み    TypeB    111    2.25
XP    搬入済み    TypeB    222    0.15
XP    搬入済み    TypeB    222    0.15
XP    搬入済み    TypeB    111    31.05
XP    搬入済み    TypeB    111    3.4
XL    2020/3/10    TypeB    222    0.5
XP    2020/3/10    TypeB    222    0.15
XL    2020/3/12    TypeB    222    3.4
XL    調整中    TypeB    222    2.85
XL    調整中    TypeB    222    0.5
XP    調整中    TypeB    222    0.5
XP    調整中    TypeB    222    0.15
XL    2020/3/18    TypeA    333    19.7
XL    2020/3/18    TypeA    333    19.65
XL    2020/3/18    TypeA    333    19.65
XL    2020/4/10    TypeA    333    7.5
XL    2020/4/10    TypeA    333    7.5
XL    2020/4/10    TypeA    333    7.5
XL    調整中    TypeA    111    19.7
XL    調整中    TypeB    111    0.5
XL    調整中    TypeA    111    19.7
XL    2020/4/8    TypeA    333    11.45
XL    2020/4/7    TypeA    333    19.65
XL    2020/4/7    TypeA    333    10.85
XL    2020/4/8    TypeA    333    11.05
XL    2020/4/8    TypeA    333    19.65
XL    2020/4/9    TypeA    333    19.65
XL    2020/4/9    TypeA    333    11.05
XL    2020/4/9    TypeA    333    29
XL    2020/4/8    TypeA    333    19.65
XL    2020/4/8    TypeA    333    29
XL    2020/3/10    TypeA    111    29
XL    2020/4/3    TypeA    333    29.05
XL    2020/4/8    TypeA    333    10.85
XL    2020/4/1    TypeA    333    11.45
XL    2020/4/3    TypeA    333    11.45
XL    2020/4/8    TypeA    333    11.45
XP    調整中    TypeB    111    0.05
XP    調整中    TypeB    111    0.05
XP    調整中    TypeB    111    15.85
XP    調整中    TypeB    111    15.85
XP    調整中    TypeB    111    15.85
XL    2020/3/10    TypeA    111    19.7
XP    調整中    TypeB    111    68
 
実際に行いたいことはB列『AAA』中の調整中を作成したピボットテーブル行2列目で
そのフィールドの一番下にドラッグして並べ替えるをマクロで行いたいのです。
 
コード、雑音を除き、少し手を加えたものを再度アップいたします。
どうぞよろしくお願いします。
 
Option Explicit
Sub test()
 
    Dim BLong As Long, AAAItemNum As Long, FindAAA As Long, xlLast As Long
    BLong = Sheets("Sheet1").Range("B5").End(xlDown).Row
      
    Dim ptblName1 As String
    ptblName1 = "ピボットテーブル1"
         
    FindAAA = Sheets("Sheet1").Range("B5:B" & BLong).Find("調整中").Row
     
     
    Sheets("Sheet1").Range("B" & FindAAA).Activate
    MsgBox "The active item is in position number " & _
    ActiveCell.PivotItem.Position
    AAAItemNum = ActiveCell.PivotItem.Position
     
    xlLast = Sheets("Sheet1").Range("A6").End(xlDown).Offset(-1, 0).Row
     
    With Sheets("Sheet1").PivotTables(ptblName1).PivotFields("AAA")
        .AutoSort xlManual, "AAA" '詳細>並べ替え>手動
       .PivotItems("調整中").Position = xlLast
    End With
     
End Sub
 

回答
投稿日時: 20/03/09 22:53:52
投稿者: simple

さきほどのコメントは取り消しとさせてください。誤認がありました。
 
ピボットテーブルの形式は、以下のものでよいのですか?

  A列       B列      C列       D列 
1 
2  
3 コード    (すべて)            
4                               
5 Type     AAA       Shippt    合計 / weight
6 TypeA    調整中    111       39.4
7          搬入済み  111       31.4
8                    333       19.65
9           2020/3/10 111       48.7
10     (---- 以下省略 -------)
という形式でよいのですか?

回答
投稿日時: 20/03/10 05:51:37
投稿者: simple

最後に持っていくなら、PivotItemsの個数を数えて、
それを、Positionに設定すればよいのでした。
これでスッキリしませんか?
 

Sub sample()
    Dim ws As Worksheet
    Dim pvt As PivotTable
    
    Set ws = Worksheets("Sheet1")
    Set pvt = ws.PivotTables(1)
    
    With pvt.PivotFields("AAA")
        .AutoSort xlManual, "AAA"
       .PivotItems("調整中").Position = .PivotItems.Count
    End With
End Sub

「調整中」と「搬入済み」は、mPositionプロパティに値を持つが、
日付データには対応するものがないのですね。
そこにこだわって時間を使ってしまいました。
 
(なお、sortを手動にする部分のコードについては、
 Helpには記載が無いような気もして自信ありません。)
 
# ちょっと腑に落ちないのは、
# ローカルウインドウに表示されるPositionプロパティの値と、
# 実際に取得したPositionプロパティの値が異なることです。
# 前者は 0 なのに、実際に取得すると0以外の値が取得できます。不思議。
# 何か不具合がある感触です。

投稿日時: 20/03/10 10:01:20
投稿者: RyuRyu

Simpleさん!!!素晴らしいです!!!
そうです。頂いたCodeのようにしたかったのです。
ばっちり動作してやりたいこともできました。
今回も大変お世話になりました。
またご教授お願いします!