emohanapriya
New Member
- Joined
- Feb 20, 2015
- Messages
- 9
I was able to get this code for creating another pivot table. but this gives the error as " Application defined or Object defined error.. please check the VBA code as below
Public w1 As Workbook
Public w2 As Workbook
Public w3 As Workbook
Private Sub CommandButton21_Click()
Application.ScreenUpdating = False
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
starttime = Time
paw1 = "C:\Risk Reporting\PGC CB data.xlsx"
Set w1 = Workbooks.Open(Filename:=paw1, UpdateLinks:=0)
w1.Activate
'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
'Create a new worksheet
Set sht = Sheets.Add
'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
--------------------------------------
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
----------------------------------------------doesnot excecute this and getting error
With ActiveSheet.PivotTables("PivotTable1").PivotFields("VC")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Name"), "Count of Name", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Over All Status")
.Orientation = xlColumnField
.Position = 1
End With
End Sub
Public w1 As Workbook
Public w2 As Workbook
Public w3 As Workbook
Private Sub CommandButton21_Click()
Application.ScreenUpdating = False
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
starttime = Time
paw1 = "C:\Risk Reporting\PGC CB data.xlsx"
Set w1 = Workbooks.Open(Filename:=paw1, UpdateLinks:=0)
w1.Activate
'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
'Create a new worksheet
Set sht = Sheets.Add
'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
--------------------------------------
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
----------------------------------------------doesnot excecute this and getting error
With ActiveSheet.PivotTables("PivotTable1").PivotFields("VC")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Name"), "Count of Name", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Over All Status")
.Orientation = xlColumnField
.Position = 1
End With
End Sub