From
noodnutt@gmail.com@21:1/5 to
All on Thu Jan 30 17:35:21 2020
Hi All
Apologies for the length, I thought it best to try and give detailed explanation as to the issue:
My companies IT Team are just shits to deal wit, it's like pulling your own teeth without novocaine.
As a result of MS Update KB2596596 way, way back on Dec 12, 2011, it negated the ability to generate Print ( Preview & PrintOut ) of Axes & Titles in Charts. If my companies IT Team had a pulse, you wouldn't know given they were not aware of this problem
until I raised it because I am generating Charts via Code.
Needless to say, I am not holding my breath in anticipation of a quick remedy from them.
So, I can generate the chart(s) via code, but then I have to switch to printing the sheet range via another code to actually get the chart to print WYSIWYG.
Moving On:
When the below code runs, it creates the chart according to the code, it changes the charts ( Shape & Title ) Name accordingly, no problemo yet, works really well, I have zero issues with it, until I generate consecutive charts.
Before the second chart is generated, the Pivot Table filter is cleared & reset to the next value, which in this case would be "T-05", and again, the charts ( Shape & Title ) Name is renamed according to the code, but!!!!!!!
When it generates(T-05), it changes the (x.y) Axes Series values of the first chart (All). Does anyone have any ideas why this is happening please...??
Now, just so you're aware, I change the following to coincide with the number of shapes in my sheet:
CC(All) = ChartObjects(1)
With shChar.ChartObjects(1)
.Activate
.Name = myChar
.Left = Range("B2").Left
.Top = Range("B2").Top
End With
CC(T-05) = ChartObjects(2)
With shChar.ChartObjects(1)
.Activate
.Name = myChar
.Left = Range("B20").Left
.Top = Range("B20").Top
End With
CC(T-06) = ChartObjects(3)
With shChar.ChartObjects(1)
.Activate
.Name = myChar
.Left = Range("B38").Left
.Top = Range("B38").Top
End With
Etc....
It works quite well aligning each of the generated charts to a cell range.
Sub Print_Charts_All()
Dim myPDsht As Worksheet: Set myPDsht = ThisWorkbook.Sheets("Centre Combo") 'My Pivot Data Sheet
Dim shChar As Worksheet: Set shChar = ThisWorkbook.Sheets("Charts") 'My Pivot Data Sheet
Dim myPT As PivotTable: Set myPT = myPDsht.PivotTables("Centre_Combo")
Dim myPF As PivotField: Set myPF = myPT.PivotFields("CC")
Dim myChart As Shape: Set myChart = shChar.Shapes.AddChart
Dim mySSDRng, myTitle As Range
Dim myChar As String
Dim Lrow As Long
Dim x As ChartObject
shChar.Select
Range("B2").Select
myChar = "CC(All)"
With shChar
With myPDsht
.PivotTables("Centre_Combo").PivotFields("CC").ClearAllFilters
.PivotTables("Centre_Combo").PivotFields("CC").CurrentPage = "All"
End With
With myChart.Chart
.SetSourceData Source:=myPDsht.Range("$A$6", myPDsht.Range("$C" & myPDsht.rows.count).End(xlUp))
.FullSeriesCollection(1).ChartType = xlLine
.FullSeriesCollection(1).AxisGroup = 1
.FullSeriesCollection(2).ChartType = xlLine
.FullSeriesCollection(2).AxisGroup = 1
.FullSeriesCollection(2).AxisGroup = 2
.ShowAllFieldButtons = False
.Parent.Top = 75
.Parent.Left = 200
.Parent.Height = 250
.Parent.Width = 750
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = myPDsht.Range("$B$4")
.Axes(xlValue, xlPrimary).Select
.Axes(xlValue, xlPrimary).MinimumScale = 0
.Axes(xlValue, xlPrimary).MaximumScale = 5000
.Axes(xlValue, xlPrimary).MajorUnit = 1000
.Axes(xlValue, xlPrimary).MinorUnit = 500
.Axes(xlValue, xlSecondary).Select
.Axes(xlValue, xlSecondary).MinimumScale = 0
.Axes(xlValue, xlSecondary).MaximumScale = 75000
.Axes(xlValue, xlSecondary).MajorUnit = 25000
.Axes(xlValue, xlSecondary).MinorUnit = 5000
.Axes(xlCategory).CategoryType = xlCategoryScale
End With
End With
Application.Wait (Now + myTI * 250)
With shChar.ChartObjects(1)
.Activate
.Name = myChar
.Left = Range("B2").Left
.Top = Range("B2").Top
End With
Range("B20").Select
End Sub
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)