How to move a newly created chart in VBA

Question

I have a procedure which creates a series of charts which appear t be created on their own sheets. The sheets are then renamed. After that I want to move all the newly created worksheets into a new workbook. The problem is that when I cycle through the worksheets in the workbook, none of the new worksheets seem to be picked up in the loop. In the Project explorer it shows them as charts not a sheet. How do I cycle through them and move them into a new workbook since it does nto pick them up as worksheets?

Thanks

    BeneficiaryNameFind = BeneficiaryList(i)
    If InStr(1, BeneficiaryList(i), "/") > 0 Then
        BeneficiaryList(i) = Replace(BeneficiaryList(i), "/", "")
    End If
    If BeneficiaryList(i) <> BeneficiaryList(i - 1) Then
      BeneficiaryName = Left(BeneficiaryList(i), 30)
      'Adding new worksheet
      Worksheets("DataforPrincipals").Activate
      Set wsnew = Worksheets.Add
      wsnew.Name = BeneficiaryName
      Worksheets(BeneficiaryName).Activate
      'Creating Pivot table
      Set objPivotTable = objPivotcache.CreatePivotTable(wsnew.Range("A1"))
      'set Beneficiary row field
      'Setting Fields
      With objPivotTable
      With .PivotFields("Business Name")
        .Orientation = xlPageField
        .CurrentPage = "ALL"
        .ClearAllFilters
        .CurrentPage = BeneficiaryNameFind
      End With
     'set data fields (PI TO, TO)
      .AddDataField .PivotFields("Pre-ignition T/O"), "PI T/O", xlSum
      .AddDataField .PivotFields("ITD Average"), "ITD", xlSum
      .AddDataField .PivotFields("Growth"), "TO Growth", xlSum
      With .PivotFields("PI T/O")
        .NumberFormat = "$ #,##0"
      End With
      With .PivotFields("ITD")
       .NumberFormat = "$ #,##0"
      End With
      With .PivotFields("TO Growth")
       .NumberFormat = "#%"
      End With
      End With
      ' Access the new PivotTable from the sheet's PivotTables collection.
      Set objPivot = ActiveSheet.PivotTables(1)
      ' Add a new chart sheet.
      Set objChart = Charts.Add
      ' Create a Range object that contains
      ' all of the PivotTable data, except the page fields.
      Set objPivotRange = objPivot.TableRange1
      ' Specify the PivotTable data as the chart's source data.
      With objChart
        .ShowAllFieldButtons = False
        .SetSourceData objPivotRange
        .ChartType = xlColumnClustered
     '   .HasDataTable = True
        .SeriesCollection(1).HasDataLabels = True
        .SeriesCollection(1).DataLabels.NumberFormat = "$ #,##0"
        .SeriesCollection(1).DataLabels.Font.Size = 14
        With .SeriesCollection(1).DataLabels.Format.Fill
          .Visible = msoTrue
          .ForeColor.ObjectThemeColor = msoThemeColorAccent1
          .ForeColor.TintAndShade = 0
          .ForeColor.Brightness = 0.6000000238
          .Transparency = 0
          .Solid
        End With
        .SeriesCollection(2).HasDataLabels = True
        .SeriesCollection(2).DataLabels.NumberFormat = "$ #,##0"
        .SeriesCollection(2).DataLabels.Font.Size = 14
        With .SeriesCollection(2).DataLabels.Format.Fill
          .Visible = msoTrue
          .ForeColor.ObjectThemeColor = msoThemeColorAccent2
          .ForeColor.TintAndShade = 0
          .ForeColor.Brightness = 0.6000000238
          .Transparency = 0
          .Solid
        End With
        .SeriesCollection(3).HasDataLabels = True
        .SeriesCollection(3).DataLabels.NumberFormat = "#%"
        .SeriesCollection(3).DataLabels.Font.Size = 14
        With .SeriesCollection(3).DataLabels.Format.Fill
          .Visible = msoTrue
          .ForeColor.ObjectThemeColor = msoThemeColorAccent3
          .ForeColor.TintAndShade = 0
          .ForeColor.Brightness = 0.400000006
          .Transparency = 0
          .Solid
        End With
      End With

Matteo NNZ Matteo NNZ · Accepted Answer · 2015-01-22T08:47:28

What you can do is to store all the newly created charts into a collection, that will hence hold the reference to the sheet:

On top of the For loop:

Dim allNewSheets As New Collection

Inside the For Loop:

Set wsnew = Worksheets.Add
allNewSheets.Add wsnew

So at the end you will have all the new worksheets into the collection to move them elsewhere:

For j = 1 To allNewSheets.Count
    'move allNewSheets(j)
Next j

suggestion: Try to use some more references: prefer always

With wsnew
    'add charts, pivots etc.
End With

rather than

wsnew.Activate
'add charts, pivots etc.

How to move a newly created chart in VBA

2 Answers