![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
お世話になります。
Excel2016にて作業をしております。
ピボットテーブルを既存のシートに作成したいのですが、上手くいかないのでお知恵をお貸しいただければと思います。
【概要】
ピボットテーブルを既存のシートに作成したい
【詳細】
複数シート(シート名は担当者名)のA2:B列をデータ範囲としてピボットテーブルを作成しようとしています。
A列とB列の行数は同じですが、シートによって最終行の位置は異なります。
新しいシートにピボットテーブルを作成するのではなく、同じシート内にピボットテーブルを作りたいと考えています。
ただこのシート名が、担当者名が書かれたセルをシート名として拾うようになっており、シート指定をどのようにすれば良いかわからず困っています。
A列 B列 C列 D列
1行目 A1セルの値がシート名 D1セルよりピボットテーブル作成開始
2行目 A B
3行目 A B
4行目 A B
5行目
(シート名:担当者名)
マクロ記録では下記のようになりましたが、
1)Range("A2:B50").Select → B列最終行を取得し、A2~B列最終行までにしたい
2)SourceData:= "担当名!R2C1:R50C2" → シート名をどのように指定すればよいかわからない
3)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
1)は
Set ws = ActiveSheet
データ範囲 = "ws!A2:B" & ws.Cells(Rows.Count, "A").End(xlUp).Row
のように指定してみていますが、うまくいきません。
お知恵をお貸しいただけますとありがたいです。
よろしくお願いいたします。
No.2ベストアンサー
- 回答日時:
「必ず、ピボットを作成したいシート(目的の担当の方のシート)をアクティブにしている」、かつ、
「複数のExcelファイルを同時に開いての処理はしない」、
ということが前提ですが・・・
以下のような感じで指定してみてはどうでしょうか?
2)SourceData:= "担当名!R2C1:R50C2"
→SourceData:= Activesheet.Name & "!R2C1:R50C2"
3)TableDestination:="担当名!R2C6"
→TableDestination:= Activesheet.Name & "!R2C6"
※Activesheet.Name は、何か変数を作って代入し、例えば以下のように書いても良いと思います。
Dim ActShtNm As String
ActShtNm = Activesheet.Name
とやっておいて・・・・
2)SourceData:= "担当名!R2C1:R50C2"
→SourceData:= ActShtNm & "!R2C1:R50C2"
3)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
- 回答日時:
No2です。
ほ・・・ほんとうにごめんなさい。「A1セルの値がシート名 」ということでしたですね。
ご質問をちゃんと読んでなくて本当にすみません。
シート名を拾際のお話ですが、私の最初の回答のプログラムコードのように、
「ActiveSheet.Name」を使ってもいいと思うのですが、
何らかの理由でそれだとエラーになる場合は・・・
ActShtNm = ActiveSheet.Name
の行を
ActShtNm = ActiveSheet.Range("A1").Value
に書き換えて試してみてください。
本当に失礼いたしました。
No.4
- 回答日時:
No2です。
何度もごめんなさい。同様に、
SourceData:= Activesheet.Name & "!R2C1:R50C2"
のところも、RangeオブジェクトでもOKでした。
なので、
SourceData:= ActiveSheet.Range("A2:B50")
といったような指定もできました。
名前定義したセル範囲も使えるようです。
例えば、「A2:B50」に、「集計ソース」という名前を定義した場合、
SourceData:= ”集計ソース”
でも行けました。
色々、失礼いたしました。
No.3
- 回答日時:
No2です。
ごめんなさい!
'ピボットの出力先は、Rangeオブジェクトじゃないといけないっぽいので
'出力先のセルをオブジェクト変数に代入
のところは、
'ピボットの出力先は、RangeオブジェクトでもOKっぽいので
'出力先のセルをオブジェクト変数に代入
の書き間違いでした。
私の前回回答のコードとしては、
TableDestination:=TargetCell, _
と書いても、
TableDestination:=ActShtNm & "!R3C4", _
と書いても、ピボットを作成できました。
大変失礼いたしました。
No.1
- 回答日時:
各シートのB列最終行を探すのは簡単で
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
みたいな感じで振り分けすれば良いと思います
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Excel(エクセル) エクセルのマクロでコピー後の貼り付け先を毎回指定したところにしたい 5 2022/08/12 10:47
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 複数csvを横に追加していくマクロについて 2 2023/04/25 09:19
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Visual Basic(VBA) 複数シート一括作成後に、特定範囲の数式は値で貼り付けしたい 3 2022/10/07 11:18
- Visual Basic(VBA) 最終行の指定について教えてください。 複数シートを1シートへまとめる下記マクロでは各シートの6行目を 1 2022/10/04 18:37
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/06/01 14:45
- Visual Basic(VBA) VBAで最新のデータを別シートに転記する方法をお教えください。 3 2022/04/07 19:20
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
既存ワークシートにピボットテーブルを作成するマクロ
Excel(エクセル)
-
ピボットテーブルでの毎回可変するデータの最終行までの範囲を指定したいです。
PowerPoint(パワーポイント)
-
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
-
4
Excel VBA ピボットテーブルにて、 最終行の取得の仕方を教えてください
Excel(エクセル)
-
5
ブックのピボットを別ブックにコピーして自動更新したい
Excel(エクセル)
-
6
VBAで文字列を数値に変換したい
Excel(エクセル)
-
7
エクセル マクロ オートフィルの終点の指定について
Excel(エクセル)
-
8
Excel、VBAでピボットテーブル、pagefieldの絞込み
Excel(エクセル)
-
9
VBAでファイルを開くときにファイル名でワイルドカードを使用したいです
その他(プログラミング・Web制作)
-
10
エクセルVBAのIf,Then 構文でOr条件とAnd条件の結合方法?
Excel(エクセル)
-
11
同じ作業を複数のシートに実行させるにはどうしたらいいのでしょうか
Visual Basic(VBA)
-
12
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
13
エクセルVBAのIF文の否定の複数条件
Excel(エクセル)
-
14
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
15
VBAでセル入力の数式に変数を用いたい
Excel(エクセル)
-
16
VBAで重複データを合算したい
Excel(エクセル)
-
17
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
18
もしセルが#N/A"なら~をする・・・には?"
Excel(エクセル)
-
19
エクセルVBAでオートフィルター最上行を取得するには
Excel(エクセル)
-
20
Excel VBA: ピボットテーブルの値貼り付け
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの保護で、列の表示や...
-
文字の色も参照 VLOOKUP
-
VBAで繰り返しコピーしながら下...
-
ExcelのVlookup関数の制限について
-
【条件付き書式】countifsで複...
-
Excel複数シートにあるデータを...
-
Excelでの並べ替えを全シートま...
-
エクセルで、チェックボックス...
-
エクセルの列の限界は255列以上...
-
VLOOKアップ関数の結果の...
-
Excel の複数シートの列幅を同...
-
【VBA】複数のシートの指定した...
-
エクセル マクロ 標準モジュー...
-
エクセル複数シートのデータを...
-
Excelに自動で行の増減をしたい...
-
Excelで全てのシートに一気に列...
-
VBAで検索して、行をコピー&追...
-
【マクロ】あいうえお順のシー...
-
accessでexcelを読み込む時のデ...
-
エクセル 日報売上を月報に展開...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
文字の色も参照 VLOOKUP
-
ExcelのVlookup関数の制限について
-
エクセルの保護で、列の表示や...
-
VBAで繰り返しコピーしながら下...
-
Excel の複数シートの列幅を同...
-
エクセルで横並びの複数データ...
-
エクセルの列の限界は255列以上...
-
【条件付き書式】countifsで複...
-
Excelでの並べ替えを全シートま...
-
SUMPRODUCTにて別シートのデー...
-
エクセル マクロ 標準モジュー...
-
VLOOKアップ関数の結果の...
-
エクセルで、チェックボックス...
-
Excel VBA ピボットテーブルに...
-
オートフィルタ使用時にCOUNTIF...
-
スプレッドシートでindexとIMPO...
-
エクセルVBAで、ある文字を含ん...
-
エクセルのブック分割マクロを...
-
【VBA】複数のシートの指定した...
-
Excel複数シートにあるデータを...
おすすめ情報