Resize a ChartSheet Chart

thatoneguy650

Board Regular
Joined
Jan 17, 2014
Messages
103
I am trying to re-size a chart that is in its own chart sheet, but none of the regular chart re-sizing methods are working. What my code does so far is make a pivot then use that pivot to make a chart, take the chart and move it to a new chart sheet. It then re-sizes it for 11x17 paper and finally it should make the chart fill up the 11x17 page.
This is what I have now:

wFbHb5Q.png


This is what I want

0y2XXSQ.png


My code is as follows:

Code:
Sub domesticOrderPivot()

Sheets("Raw").Activate
Columns("A:L").Select


Sheets.Add.Name = "DomesticOrderPivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Raw!R1C1:R1048576C12", Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:="DomesticOrderPivot!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion12
Sheets("DomesticOrderPivot").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Order Key"), "Count of Order Key", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Rank")
    .Orientation = xlPageField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Rank").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Rank").CurrentPage = "1"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Market and Segment")
    .Orientation = xlColumnField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Ship Date")
    .Orientation = xlRowField
    .Position = 1
End With
Range("A14").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
    False, True, False, True)
ActiveWindow.SmallScroll Down:=-24
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Market and Segment")
    .PivotItems("Domestic - ").Visible = False
    .PivotItems("Domestic - Non-Prime").Visible = False
    .PivotItems("Domestic - Publication").Visible = False
    .PivotItems("Export").Visible = False
    .PivotItems("NJA").Visible = False
    .PivotItems("NPI").Visible = False
    .PivotItems("(blank)").Visible = False
End With


ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'DomesticOrderPivot'!$A$3:$E$85")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Domestic Orders"
Sheets("Domestic Orders").PageSetup.PaperSize = xlPaper11x17
Sheets("Domestic Orders").Activate
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Year and Month"
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Count of Orders"
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Count of Domestic Orders from 2008"
Sheets("Domestic Orders").ChartObjects(1).Select
     With ActiveChart.Parent
         .Height = 3250 ' resize
         .Width = 5000  ' resize
         .Top = 100    ' reposition
         .Left = 100   ' reposition
     End With
End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,214,798
Messages
6,121,636
Members
449,043
Latest member
farhansadik

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