Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
大量にあるピボットテーブルのデータソース変更
投稿日時: 18/05/16 09:43:18
投稿者: TANPOPO

こんにちは。
 
1つのブックに複数のシートと複数のピボットテーブルがあります。
ピボットテーブルのデータソースは全て同じです。
データソースが変更になった際、一気に直したいので下記のマクロを準備しました。
 
 

Sub ピボ一覧()

  Dim sh As Worksheet
  Dim pvt As PivotTable
  Dim n As Long 

  Worksheets.Add Before:=Sheets(1)

  Range("A1:C1").Value = _
    Split("シート名;ピボット名;sub macro000()", ";")

  n = 2
  For Each sh In Worksheets
    For Each pvt In sh.PivotTables
      Cells(n, "A").Value = sh.Name
      Cells(n, "B").Value = pvt.Name
      Cells(n, "C").Value = "=""Worksheets(""""""&RC[-2]&"""""").PivotTables(""""""&RC[-1]&"""""").ChangePivotCache (""""""&R2C1&""!""&R2C2&"""""")"""
      n = n + 1
      
    Next pvt
  Next sh

  Range("A1:C1").EntireColumn.AutoFit
      n = Cells(Rows.Count, "C").End(xlUp).Row + 1
    Range("C" & n).Select
    ActiveCell.FormulaR1C1 = "End Sub"
  Range("C1").Select
   
  MsgBox "完成"

End Sub

 
このマクロを実行すると、
A列にはシート名、B列にはピボットテーブル名を新しいブックに表示してくれます。
C列には下記関数が入っています。
="Worksheets("""&A2&""").PivotTables("""&B2&""").ChangePivotCache ("""&$A$2&"!"&$B$2&""")"
 
こちらの関数でできたC列をモジュールに貼り付けて実行すると、
ピボットテーブルのデータソースが一気に変更できます。
 
手順としては、
@一番左側のデータソースを変更する
AVBを実行する
BC列をコピーしてモジュールに貼り付けて実行する
という手順になっています。
この方法で大量にあるピボットテーブルのデータソース変更が楽にはなったのですが、
手順が多いのでもっと簡単にする方法はないだろうかと思い、投稿させていただきました。
 
なにか良い方法があればご教授いただければ幸いです。
 
よろしくお願いします。

回答
投稿日時: 18/05/16 10:46:45
投稿者: Suzu

こんにちは。
 
このコードは、どなたがお作りになったのでしょう?
質問者さんの技術レベルが良く分からないです。。
 
質問者さんがお作りになったのなら、
 
・サブルーチンを作ってそこに渡しても良いし
・単純に、このコードの中で ChangePivotCacheメソッドを実行しても良いし。
 
で、ヒントにはなるでしょう。
 
 
別の方がつくられたのなら、
 
通常は、ワークシートに、VBAのコードを生成し表示させないで
【このコードの中で ChangePivotCacheメソッドを実行しても良いし】
がなされているはずです。
 
それをせず、わざわざ とも言える事を行っているのですから、何かしらの意図があるのでは?
作成者の方に確認しましょう。

投稿日時: 18/05/16 16:37:03
投稿者: TANPOPO

Suzuさん、ありがとうございます。
 

引用:
このコードは、どなたがお作りになったのでしょう?

大量のピボットテーブルのリンク先を一括変換したい。という目標の元、
先ずはシート名とピボットテーブル名を出そうと考え、
検索してヒットしたコードを編集させていただいたものです。

回答
投稿日時: 18/05/16 17:05:23
投稿者: mattuwan44

>なにか良い方法があればご教授いただければ幸いです。
ピボットテーブルの元データを差し替える操作を
マクロの記録でコード化してみれば解るのでは?

回答
投稿日時: 18/05/17 09:50:29
投稿者: Suzu

引用:
先ずはシート名とピボットテーブル名を出そうと考え、
検索してヒットしたコードを編集させていただいたものです。

 
との事ですので、ワークシートに [シート名] [ピボット名] は出力しなくても良いのですよね。
 
 
例えば
 
全てのワークシート名をメッセージボックスにて表示
Sub MainRoutine()
  Dim wst As WorkSheet
 
  For Each wst In ThisWorkBook.WorkSheets
    MsgBox wst.Name
  Next
End if
 
セルA1に「アクティブな」ワークシートの名称を表示したい時のコード
Sub subRoutine()
  Range("A1").Value = ActiveSheet.Name
End Sub
 
 
これらを組み合わせて
 
全てのワークシートのセルA1にそのワークシート名称を表示したい。
 
を実現させようとしたとき
 
 
メインルーチン/サブルーチンと分けて
-----------------------------------------------
Sub MainRoutine()
  Dim wst As WorkSheet
 
  For Each wst In ThisWorkBook.WorkSheets
    Call subRoutine(wst)
  Next
End if
 
Sub subRoutine(wst1 As WorkSheet)
  wst1.Range("A1").Value = wst1.Name
End Sub
-----------------------------------------------
と言う方法や
 
 
 
一つにしてしまえば
-----------------------------------------------
Sub MainRoutine()
  Dim wst As WorkSheet
 
  For Each wst In ThisWorkBook.WorkSheets
    wst.Range("A1").Value = wst.Name
  Next
End if
-----------------------------------------------
 
の様にできます。
 
 
通常コードはもっと長くなりますのでメイン/サブ に分けた場合
 
メインはメイン、サブはサブ 独立して管理でき
・役割で分ける事が多いので、コードが判り易い
・サブ側で修正したコードの内容はメイン側に影響させない事が可能。
    →メンテナンスし易い
 
また、今回の様にあるコードができていて、そこに機能追加する際
追加する部分のコードを別に書き、メイン側をちょっといじると適用できる
というメリットもあります。
 
 
一つにしてしまう方法は、短いコードの時には有効でしょう
今回もコード的には短いですね。
 
 
メイン/サブ にするか
一つにするかはお好みでどうそ。

トピックに返信