Urgent Help required on charts

A

adr12345

Board Regular
Joined
Jan 19, 2015
Messages
67
Hi I am writing this code and getting stuck at line

oChart2.SetSourceData Source:=PT2.TableRange1




Dim data1, data2 As PivotCache
Dim PT1, PT2, PT3, PT4, PT5 As PivotTable
For Each ws In Worksheets
If ws.Name = "Bidding" Then
Application.DisplayAlerts = False
Worksheets("Bidding").Delete
Application.DisplayAlerts = True
End If
Next
'Worksheet.Add
Worksheets.Add before:=Sheets("BOE")
ActiveSheet.Name = "Bidding"
Sheets("CR").Select
Dim A, B As Integer
A = Range("A1", Range("A1").End(xlToRight)).COUNT
B = Range("A1", Range("A1").End(xlDown)).COUNT

Set data2 = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1", Cells(B, A)).Address(, , xlR1C1))
'====================================================
'==== TABLE 1 = Sole Ethernet========================
'====================================================

Set PT1 = data2.CreatePivotTable(Worksheets("Bidding").Range("B3"))
With PT1
With .PivotFields("New Access Tech")
.Orientation = xlColumnField
.Position = 1
End With
'PT1.PivotFields("New Access Tech").CurrentColumn = "Ethernet"
With PT1
With .PivotFields("Multiple Bids")
.Orientation = xlPageField
.Position = 1
End With
PT1.PivotFields("Multiple Bids").CurrentPage = "Sole"

With .PivotFields("CR Carrier")
.Orientation = xlRowField
.Position = 1
End With
'With PT1.PivotFields("CR Quotes Z-NZ")
'.Orientation = xlDataField
'.Function = xlSum
'.Position = 1
'.Caption = "Bids"
'End With
With PT1.PivotFields("CR=WINNER")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.Caption = "Wins"
End With
With PT1
.RowGrand = False
.MergeLabels = True
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.SmallGrid = True
.ShowTableStyleColumnHeaders = True
.LayoutRowDefault = xlTabularRow
End With
End With
End With
Dim oChart1 As chart
Set oChart1 = PT1.Parent.Shapes.AddChart(XlChartType:=xlstackedBar).chart
oChart1.SetSourceData Source:=PT1.TableRange1
With oChart1
.HasTitle = True
.ChartTitle.Text = "Sole Ethernet Wins"
.ShowAllFieldButtons = False
End With
With oChart1.PlotArea.FORMAT.Fill
.ForeColor.RGB = RGB(224, 224, 224)
'.Transparency = 0
End With
With oChart1.ChartArea.FORMAT.Fill
.ForeColor.RGB = RGB(224, 224, 224)
'.Transparency = 0
End With
With oChart1.Axes(xlValue)
.DisplayUnit = xlThousands
End With
'====================================================
'==== TABLE 2 = Sole LL==============================
'====================================================

Set PT2 = data2.CreatePivotTable(Worksheets("Bidding").Range("B23"))
With PT2
With .PivotFields("New Access Tech")
.Orientation = xlColumnField
.Position = 1
.PivotItems("LL").Visible = False
End With
'PT1.PivotFields("New Access Tech").CurrentColumn = "Ethernet"
With PT2
With .PivotFields("Multiple Bids")
.Orientation = xlPageField
.Position = 1
End With
PT2.PivotFields("Multiple Bids").CurrentPage = "Sole"

With .PivotFields("CR Carrier")
.Orientation = xlRowField
.Position = 1
End With
'With PT1.PivotFields("CR Quotes Z-NZ")
'.Orientation = xlDataField
'.Function = xlSum
'.Position = 1
'.Caption = "Bids"
'End With
With PT2.PivotFields("CR=WINNER")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.Caption = "Wins"
End With
With PT2
.RowGrand = False
.MergeLabels = True
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.SmallGrid = True
.ShowTableStyleColumnHeaders = True
.LayoutRowDefault = xlTabularRow
End With
End With
End With
Dim oChart2 As chart
Set oChart2 = PT2.Parent.Shapes.AddChart(XlChartType:=xlBar).chart
oChart2.SetSourceData Source:=PT2.TableRange1
With oChart2
.HasTitle = True
.ChartTitle.Text = "Sole Ethernet Wins"
.ShowAllFieldButtons = False
End With
With oChart2.PlotArea.FORMAT.Fill
.ForeColor.RGB = RGB(224, 224, 224)
'.Transparency = 0
End With
With oChart2.ChartArea.FORMAT.Fill
.ForeColor.RGB = RGB(224, 224, 224)
'.Transparency = 0
End With
With oChart2.Axes(xlValue)
.DisplayUnit = xlThousands
End With


End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Hi

I'm going offline now but will work on this as soon as possible...
 
Upvote 0
Hi,

Would appreciate it. As I am not able to move any further in my vba code. Kinda stuck at that level.
I tried everything but problem is still there.
 
Upvote 0
Hi

This worked for me:

Code: 
Sub Adr_12345()
Dim data1, data2 As PivotCache, PT1, PT2, PT3, PT4, PT5 As PivotTable, A%, B%, ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "Bidding" Then
        Application.DisplayAlerts = False
        Worksheets("Bidding").Delete
        Application.DisplayAlerts = True
    End If
Next
Worksheets.Add before:=Sheets("BOE")
ActiveSheet.Name = "Bidding"
Sheets("CR").Activate
A = Range("A1", Range("A1").End(xlToRight)).Count
B = Range("A1", Range("A1").End(xlDown)).Count


Set data2 = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1", Cells(B, A)).Address(, , xlR1C1))
'====================================================
'==== TABLE 1 = Sole Ethernet========================
'====================================================


Set PT1 = data2.CreatePivotTable(Worksheets("Bidding").Range("B3"))
With PT1
With .PivotFields("New Access Tech")
    .Orientation = xlColumnField
    .Position = 1
End With
With PT1
With .PivotFields("Multiple Bids")
.Orientation = xlPageField
.Position = 1
End With
PT1.PivotFields("Multiple Bids").CurrentPage = "Sole"
With .PivotFields("CR Carrier")
.Orientation = xlRowField
.Position = 1
End With
'With PT1.PivotFields("CR Quotes Z-NZ")
'.Orientation = xlDataField
'.Function = xlSum
'.Position = 1
'.Caption = "Bids"
'End With
With PT1.PivotFields("CR=WINNER")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 1
    .Caption = "Wins"
End With
With PT1
    .RowGrand = False
    .MergeLabels = True
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .SmallGrid = True
    .ShowTableStyleColumnHeaders = True
    .LayoutRowDefault = xlTabularRow
End With
End With
End With
Dim oChart1 As Chart
Set oChart1 = PT1.Parent.Shapes.AddChart(XlChartType:=xlBar).Chart
oChart1.SetSourceData Source:=PT1.TableRange1
With oChart1
.HasTitle = True
.ChartTitle.Text = "Sole Ethernet Wins"
.ShowAllFieldButtons = False
End With
With oChart1.PlotArea.Format.Fill
.ForeColor.RGB = RGB(224, 224, 225)
.Transparency = 0
End With
With oChart1.ChartArea.Format.Fill
.ForeColor.RGB = RGB(224, 224, 225)
.Transparency = 0
End With
oChart1.Axes(xlValue).DisplayUnit = xlThousands


'====================================================
'==== TABLE 2 = Sole LL==============================
'====================================================


Set PT2 = data2.CreatePivotTable(Worksheets("Bidding").Range("B23"))
With PT2
With .PivotFields("New Access Tech")
    .Orientation = xlColumnField
    .Position = 1
    .PivotItems("LL").Visible = False
End With
'PT1.PivotFields("New Access Tech").CurrentColumn = "Ethernet"
With PT2
With .PivotFields("Multiple Bids")
    .Orientation = xlPageField
    .Position = 1
End With
PT2.PivotFields("Multiple Bids").CurrentPage = "Sole"
With .PivotFields("CR Carrier")
    .Orientation = xlRowField
    .Position = 1
End With
'With PT1.PivotFields("CR Quotes Z-NZ")
'.Orientation = xlDataField
'.Function = xlSum
'.Position = 1
'.Caption = "Bids"
'End With
With PT2.PivotFields("CR=WINNER")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 1
    .Caption = "Wins"
End With
With PT2
    .RowGrand = False
    .MergeLabels = True
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .SmallGrid = True
    .ShowTableStyleColumnHeaders = True
    .LayoutRowDefault = xlTabularRow
End With
End With
End With
Dim oChart2 As Chart
Worksheets("Bidding").Activate
Range("b23").Activate
' source data based on active cell
Set oChart2 = PT2.Parent.Shapes.AddChart(XlChartType:=xlBar).Chart
With oChart2
    .HasTitle = True
    .ChartTitle.Text = "Sole Ethernet Wins"
    .ShowAllFieldButtons = False
End With
With oChart2.PlotArea.Format.Fill
    .ForeColor.RGB = RGB(224, 224, 225)
    .Transparency = 0
End With
With oChart2.ChartArea.Format.Fill
    .ForeColor.RGB = RGB(224, 224, 224)
    .Transparency = 0
End With
With oChart2.Axes(xlValue)
    .DisplayUnit = xlThousands
End With
End Sub
 
Upvote 0
I placed the active cell inside the pivot table. When the code adds the chart, its source data is linked to that table.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
900
Steve48
S
K
  • Question Question
VBA Code to run the same Macro to all Excel file in the same folder and save and close
Replies
0
Views
953
KunaiForce
K
X
  • Question Question
How to get range of dynamic data for Pivot Table SourceData?
Replies
1
Views
704
Alex Blakenburg
A
S
  • Question Question
Help with loops to create pivot tables and arrange graphs in different orders
Replies
0
Views
729
serrulate
S
L
  • Question Question
VBA - Multiple Sheets - Create Pivot Charts on each sheet
Replies
0
Views
557
LNG2013
L

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top