【VBA】ピボットテーブルを既存のシートに作成したい

お世話になります。
Excel2016にて作業をしております。
ピボットテーブルを既存のシートに作成したいのですが、上手くいかないのでお知恵をお貸しいただければと思います。

【概要】
ピボットテーブルを既存のシートに作成したい

【詳細】
複数シート（シート名は担当者名）のA2：B列をデータ範囲としてピボットテーブルを作成しようとしています。
A列とB列の行数は同じですが、シートによって最終行の位置は異なります。
新しいシートにピボットテーブルを作成するのではなく、同じシート内にピボットテーブルを作りたいと考えています。
ただこのシート名が、担当者名が書かれたセルをシート名として拾うようになっており、シート指定をどのようにすれば良いかわからず困っています。

　　　 A列　　　B列　　　C列　　　D列
1行目　A1セルの値がシート名　　　　D1セルよりピボットテーブル作成開始
2行目　A　　　　B
3行目　A　　　　B
4行目　A　　　　B
5行目
（シート名：担当者名）

マクロ記録では下記のようになりましたが、
１）Range("A2:B50").Select　→　B列最終行を取得し、A2～B列最終行までにしたい
２）SourceData:= "担当名!R2C1:R50C2"　→　シート名をどのように指定すればよいかわからない
３）TableDestination:="担当名!R2C6"　→　シート名をどのように指定すればよいかわからない
この3点で悩んでいます。

Sub Macro()
Range("A2:B50").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"担当名!R2C1:R50C2", Version:=6).CreatePivotTable TableDestination:="担当名!R2C4" _
, TableName:="ピボットテーブル2", DefaultVersion:=6
Sheets("担当名").Select
Cells(2, 4).Select
End Sub

１）は
Set ws = ActiveSheet
　 データ範囲 = "ws!A2:B" & ws.Cells(Rows.Count, "A").End(xlUp).Row
のように指定してみていますが、うまくいきません。
お知恵をお貸しいただけますとありがたいです。
よろしくお願いいたします。

No.2 ベストアンサー 回答者： komugi3333 回答日時： 2019/05/24 13:59

「必ず、ピボットを作成したいシート（目的の担当の方のシート）をアクティブにしている」、かつ、

「複数のExcelファイルを同時に開いての処理はしない」、
ということが前提ですが・・・

以下のような感じで指定してみてはどうでしょうか？

２）SourceData:= "担当名!R2C1:R50C2"　

→SourceData:= Activesheet.Name & "!R2C1:R50C2"


３）TableDestination:="担当名!R2C6"

→TableDestination:= Activesheet.Name & "!R2C6"


※Activesheet.Name は、何か変数を作って代入し、例えば以下のように書いても良いと思います。

Dim ActShtNm As String
ActShtNm = Activesheet.Name

とやっておいて・・・・

２）SourceData:= "担当名!R2C1:R50C2"　
→SourceData:= ActShtNm & "!R2C1:R50C2"

３）TableDestination:="担当名!R2C6"
→TableDestination:= ActShtNm & "!R2C6"

と、やってもいいと思います。

=========================================

一応、ご提示のマクロの記録のコードをもとに少し書き換えるなら、以下のような感じになるのではないかと思います。


Sub test998()

　　Dim ActShtNm　　As String　　'現在アクティブなシート名を代入する変数
　　Dim LastRow　　 As Long　　　'A列とB列の最終行を代入する変数
　　Dim SourceArea　As String　　'ピボットのソースのセル範囲を代入する変数
　　Dim TargetCell　As Range　 　'ピボットを作成したい出力先のセルを代入するオブジェクト変数
　　
　　
　　'変数を使って、ピボットのソースのセル範囲を指定する形に変えたので、
　　'このセル範囲の選択操作は不要になったので、コメントアウトします。
'　　Range("A2:B50").Select
　　
　　'A列とB列の行数は同じなので、A列で最終行をチェックして変数に代入
　　LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
　　
　　'現在開いている（＝アクティブな）シートの名前を変数に代入
　　ActShtNm = ActiveSheet.Name
　　
　　'A列とB列のセル範囲を変数に代入
'　　SourceArea = ActShtNm & "!R2C1:R" & LastRow & "C2"　　'こっちの書き方でもOK。（R1C1形式）
　　SourceArea = ActShtNm & "!A2:B" & LastRow　　　　　　 'こっちの書き方でもOK。（A1形式）
　　
　　'ピボットの出力先は、Rangeオブジェクトじゃないといけないっぽいので
　　'出力先のセルをオブジェクト変数に代入
　　Set TargetCell = ActiveSheet.Range("D3")


　　ActiveWorkbook.PivotCaches. _
　　　　　　Create( _
　　　　　　SourceType:=xlDatabase, _
　　　　　　SourceData:=SourceArea) _
　　　　　　.CreatePivotTable _
　　　　　　TableDestination:=TargetCell, _
　　　　　　TableName:="test2"
　　　　　　
　　'「Version:=6」や「DefaultVersion:=6」を省略したので、
　　'もしエラーが出るようでしたら
　　'SourceData:=SourceArea や
　　'TableName:="test2" のあとに
　　'カンマ付きで復活させてください。
　　　　　　
　　Sheets(ActShtNm).Select
　　Cells(2, 4).Select

End Sub


動かなかったらごめんなさい。

No.5 回答者： komugi3333 回答日時： 2019/05/24 14:41

No2です。

ほ・・・ほんとうにごめんなさい。

「A1セルの値がシート名　」ということでしたですね。
ご質問をちゃんと読んでなくて本当にすみません。

シート名を拾際のお話ですが、私の最初の回答のプログラムコードのように、
「ActiveSheet.Name」を使ってもいいと思うのですが、
何らかの理由でそれだとエラーになる場合は・・・

ActShtNm = ActiveSheet.Name

の行を

ActShtNm = ActiveSheet.Range("A1").Value

に書き換えて試してみてください。

本当に失礼いたしました。

この回答へのお礼

詳細何度もありがとうございました！
丁寧に色々と考えてくださって本当に感謝しております。

お礼日時：2019/05/29 07:55

No.4 回答者： komugi3333 回答日時： 2019/05/24 14:20

No2です。

何度もごめんなさい。

同様に、
SourceData:= Activesheet.Name & "!R2C1:R50C2"
のところも、RangeオブジェクトでもOKでした。

なので、
SourceData:= ActiveSheet.Range("A2:B50")
といったような指定もできました。

名前定義したセル範囲も使えるようです。

例えば、「A2:B50」に、「集計ソース」という名前を定義した場合、
SourceData:= ”集計ソース”
でも行けました。

色々、失礼いたしました。

No.3 回答者： komugi3333 回答日時： 2019/05/24 14:09

No２です。

ごめんなさい！

　'ピボットの出力先は、Rangeオブジェクトじゃないといけないっぽいので
　'出力先のセルをオブジェクト変数に代入

のところは、

　'ピボットの出力先は、RangeオブジェクトでもOKっぽいので
　'出力先のセルをオブジェクト変数に代入

の書き間違いでした。



私の前回回答のコードとしては、

　　TableDestination:=TargetCell, _

と書いても、

　　TableDestination:=ActShtNm & "!R3C4", _

と書いても、ピボットを作成できました。

大変失礼いたしました。

No.1 回答者： rukaandkaito 回答日時： 2019/05/23 08:41

各シートのＢ列最終行を探すのは簡単で

Range(Range("A2:B2"), Range("A2:B2").End(xlDown))
これでそれぞれ最終行まで選択してくれます
また、シート名の取得ということですが、全てのシートが対象であれば
Dim objSheet As Worksheet

For Each objSheet In Worksheets
with objSheet

end with
Next
こんな感じでワークシート全てをループする方法ではいかがでしょうか
その中で特定のワークシートのみ作業を行いたいという事であれば
Forの中でシート名が取得できますので
For Each objSheet In Worksheets
with objSheet
select case .Name
case "田中"
case "佐藤"
case "大城"
case else
exit sub
end with
Next
みたいな感じで振り分けすれば良いと思います

この回答へのお礼

ありがとうございます！

お礼日時：2019/05/29 07:57