全般
エクセル2010 ピボットテーブルをマクロで組むとエラー5が表示され...
エクセル2010 ピボットテーブルをマクロで組むとエラー5が表示されてしまいます。
どのように修正すればいいのかどなたか教えて下さい。
マクロ全文長くて入りきらないです。 エラー部
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"コピー先!R1C1:R15000C38", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル1", DefaultVersion _
:=xlPivotTableVersion14
マクロ全文
Sub 変換()
'
' 変換 Macro
'
'
Selection.Copy
Sheets("コピー先").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("P:P").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("P1").Select
ActiveCell.FormulaR1C1 = "回線"
ActiveCell.Characters(1, 2).PhoneticCharacters = "カイセン"
Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=""フレッツ"",RC[-1],INDEX(R2C[-1]:R15000C[-1],MAX(INDEX((R2C[-13]:R15000C[-13]=RC[-13])*(R2C[-2]:R15000C[-2]=""フレッツ"")*ROW(R1C[-13]:R14999C[-13]),))))"
Range("P2").Select
Selection.Copy
Range("P2:P15000").Select
ActiveSheet.Paste
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R1").Select
ActiveCell.FormulaR1C1 = "オプション"
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""",RC[-3],IF(RC[-1]=""出張設定サポート"",RC[9],RC[-1]))"
Range("R2").Select
Selection.Copy
Range("R2:R15000").Select
ActiveSheet.Paste
Columns("V:V").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("V1").Select
ActiveCell.FormulaR1C1 = "ＣＢ①"
Range("V2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""",MAX(INDEX((R2C3:R15000C3=RC[-19])*R2C21:R15000C21,)),RC[-1])"
Range("V2").Select
Selection.Copy
Range("V2:V15000").Select
ActiveSheet.Paste
Columns("Y:Y").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Y1").Select
ActiveCell.FormulaR1C1 = "ＣＢ②"
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""",MAX(INDEX((R2C3:R15000C3=RC[-22])*R2C24:R15000C24,)),RC[-1])"
Range("Y2").Select
Selection.Copy
Range("Y2:Y15000").Select
ActiveSheet.Paste
Range("H2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2120050"
Range("G1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"コピー先!R1C1:R15000C38", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("前確日")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("後確日")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("申込管理ID")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("獲得部署")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("獲得部署責任者")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("営業担当者番号: 社員番号")
.Orientation = xlRowField
.Position = 6
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("営業担当者名")
.Orientation = xlRowField
.Position = 7
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("前確担当者番号: 社員番号")
.Orientation = xlRowField
.Position = 8
End With
With ActiveSheet.PivotTa
コメントする
