Multiple Charts and Worksheets

[fletchspur]

I have inhrited the following code to place a pivot table and chart on a worksheet. Does anyone know how I can place more than one chart on more than 1 worksheet?

Sub QC_PostProcessing()
Dim MainWorksheet As Worksheet ' Make sure your worksheet name matches!
Set MainWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Dim DataRange As Range
Set DataRange = MainWorksheet.UsedRange ' Now that you have the data in DataRange you can process it.
Dim PC As PivotCache
DataRange.Name = "DataRange"

Dim RangeName As String
If InStr(1, DataRange.Name, "=") Then
RangeName = Right(DataRange.Name, Len(DataRange.Name) - 1)
End If

Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=RangeName) '"Sheet1!R1C1:R3C4")
Dim PT As PivotTable
Set PT = PC.CreatePivotTable(TableDestination:=MainWorksheet.Cells(DataRange.Rows.Count + 2, 1), TableName:="PivotTable1") ' create table
With PT.PivotFields("Assigned_To")
.Orientation = xlRowField
.Position = 1
End With

With PT.PivotFields("Status")
.Caption = "Count of Status"
.Orientation = xlColumnField
.Orientation = xlDataField
.Position = 1
End With

MainWorksheet.Select ' add chart
Dim chrt As Chart
Charts.Add
Set chrt = ActiveChart
chrt.SetSourceData Source:=PT.RowRange
chrt.ChartType = xlColumnStacked
chrt.Location Where:=xlLocationAsNewSheet
End Sub

[Cheeky Charlie]

Hi, please use code tags to surround your code, available in Edit-> Edit Advanced. You'd be better off posting programming questions in the programming forum, please make a note here if you decide to raise your thread elsewhere.

CC