|
ちょっと面倒ですが 教えて貰えないでしょうか
工番pを入力して 4つのピボットテーブルから工番分の詳細を表示させたいのですが 該当工番がない場合には error処理で次のピボットテーブルの抽出に移るようにしました。ところが4番目”昨年原価2"の段階で 該当工番がない場合にはon error goto 終わりとしているに拘わらず 何故かerrorが出て脱出できません。構文の誤りをお教え下さい。
Sub 工番集計()
p = InputBox("工番を入力して下さい")
'原価集計から抽出
Sheets("原価集計").Select
a = Range("A2500").End(xlUp).Row
b = Range("HA2").End(xlToLeft).Column
Range(Cells(1, 1), Cells(a, b)).Name = "kouban"
Range("KOUBAN").Select
On Error GoTo zenkouban
Selection.FIND(What:=p, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, b - 1).Select
Selection.ShowDetail = True
Q = ActiveSheet.Name
L = Range("a50000").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Q & "!R1c1:R" & L & "C19").CreatePivotTable TableDestination:="原価計算!r40C1", _
TableName:="ピボットテーブル2"
Sheets("原価計算").Select
' ActiveSheet.PivotTables("ピボットテーブル2").SmallGrid = False
With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("仕入先")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("税抜金額")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
Sheets(Q).Select
ActiveWindow.SelectedSheets.Delete
'昨年原価からの抽出
zenkouban:
Sheets("昨年原価").Select
a = Range("A2500").End(xlUp).Row
b = Range("HA2").End(xlToLeft).Column
Range(Cells(1, 1), Cells(a, b)).Name = "zenkouban"
Range("zenKOUBAN").Select
On Error GoTo 注文台帳
Selection.FIND(What:=p, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, b - 1).Select
Selection.ShowDetail = True
r = ActiveSheet.Name
L = Range("a50000").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
r & "!R1c1:R" & L & "C19").CreatePivotTable TableDestination:="原価計算!r40C3", _
TableName:="ピボットテーブル3"
Sheets("原価計算").Select
' ActiveSheet.PivotTables("ピボットテーブル2").SmallGrid = False
With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("仕入先")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("税抜金額")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
Sheets(r).Select
ActiveWindow.SelectedSheets.Delete
'注文台帳からの抽出
注文台帳:
Sheets("原価集計2").Select
a = Range("A2500").End(xlUp).Row
b = Range("HA2").End(xlToLeft).Column
Range(Cells(1, 1), Cells(a, b)).Name = "kouban"
Range("KOUBAN").Select
On Error GoTo 前注文
Selection.FIND(What:=p, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, b - 1).Select
Selection.ShowDetail = True
Q = ActiveSheet.Name
L = Range("a50000").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Q & "!R1c1:R" & L & "C19").CreatePivotTable TableDestination:="原価計算!r40C6", _
TableName:="ピボットテーブル4"
Sheets("原価計算").Select
' ActiveSheet.PivotTables("ピボットテーブル2").SmallGrid = False
With ActiveSheet.PivotTables("ピボットテーブル4").PivotFields("発注先")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル4").PivotFields("金額")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
Sheets(Q).Select
ActiveWindow.SelectedSheets.Delete
'昨年原価2からの抽出
前注文:
Sheets("昨年原価2").Select
a = Range("A2500").End(xlUp).Row
b = Range("HA2").End(xlToLeft).Column
Range(Cells(1, 1), Cells(a, b)).Name = "zenkouban"
Range("zenKOUBAN").Select
On Error GoTo 終わり
Selection.FIND(What:=p, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, b - 1).Select
Selection.ShowDetail = True
r = ActiveSheet.Name
L = Range("a50000").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
r & "!R1c1:R" & L & "C27").CreatePivotTable TableDestination:="原価計算!r40C8", _
TableName:="ピボットテーブル5"
Sheets("原価計算").Select
' ActiveSheet.PivotTables("ピボットテーブル2").SmallGrid = False
With ActiveSheet.PivotTables("ピボットテーブル5").PivotFields("発注先")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル5").PivotFields("金額")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
Sheets(r).Select
ActiveWindow.SelectedSheets.Delete
Sheets("原価計算").Select
Cells.Select
Selection.Columns.AutoFit
終わり:
End Sub
|
|