Secondary axis 1

[bodo62]

Hello there.

I've been trying the whole morning to make it work, but nothing is working.

I have got a loop which copies worksheets from a folder to a new workbook, while doing the loop it builds charts for each worksheet. So far so good, now I need a secondary Axis to my charts, but I'd need some help to make it work.

Here is part of the code that builds the charts and I need to make the last column (P) of the worksheet to be a secondary axis and of xlLineMarkers type:

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 2) <> "CO" Then
If Left(ws.Name, 1) <> "_" Then
    Charts.Add After:=ws
        
    With ActiveChart
            Application.StatusBar = "Please be patient, now updating DR charts... " & ws.Name
              lRow = ws.Range("D65536").End(xlUp).Row
              .SetSourceData Source:=ws.Range("D1:P" & lRow), PlotBy:=xlColumns
                            
              .Location Where:=xlLocationAsNewSheet, Name:="DR_" & ws.Name
          
              .HasTitle = True
              .ChartTitle.Font.Bold = True
              .ChartTitle.Characters.Text = "DELIVERY REMARKS " & ws.Range("A2") & " (" & ws.Range("C2") & ")"
              .Axes(xlCategory, xlPrimary).HasTitle = True
              .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
              .Axes(xlValue, xlPrimary).HasTitle = True
              .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ""
              .HasLegend = False
              .HasDataTable = True
              .DataTable.ShowLegendKey = True
      
              ActiveChart.PlotArea.Interior.ColorIndex = 19
              ActiveChart.DataTable.Font.Size = 8
              
                            
              ActiveChart.Pictures.Insert("C:\tempfile\Sl.jpg").Select
             
             .ChartType = xlColumnStacked
            
             .PageSetup.Orientation = xlLandscape
                        .PageSetup.LeftMargin = Application.InchesToPoints(0.25)
                        .PageSetup.RightMargin = Application.InchesToPoints(0.25)
                        .PageSetup.CenterHorizontally = True
                        .PageSetup.LeftFooter = "&D"
                        .PageSetup.CenterFooter = "&P(&N)"
                        .PageSetup.RightFooter = "&A"
                        
     End With
End If
End If

Next

Thanks in andvance for nay help.

/Bodo//

 

[SkipVought]

I have emphasized where the code has been modified.

You cannot have mixed column chart types (ie stacked and not stacked) so the new chart type is line. Choose whatever type you want. You will want to add annotations and formatting to the secondary axis as well.

            With ActiveChart
               Application.StatusBar = "Please be patient, now updating DR charts... " & WS.Name
                 lRow = WS.Range("D65536").End(xlUp).Row
                 .SetSourceData Source:=WS.Range("D1:P" & lRow), PlotBy:=xlColumns
                               
                 .Location Where:=xlLocationAsNewSheet, Name:="DR_" & ws.Name
                [b].ChartType = xlColumnStacked
                  With .SeriesCollection(.SeriesCollection.Count)
                     .AxisGroup = 2
                     .ChartType = xlLineMarkers
                  End With[/b]
                 .HasTitle = True
                 .ChartTitle.Font.Bold = True
                 .ChartTitle.Characters.Text = "DELIVERY REMARKS " & WS.Range("A2") & " (" & WS.Range("C2") & ")"
                 .Axes(xlCategory, xlPrimary).HasTitle = True
                 .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
                 .Axes(xlValue, xlPrimary).HasTitle = True
                 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ""
                 .HasLegend = False
                 .HasDataTable = True
                 .DataTable.ShowLegendKey = True
         
                 ActiveChart.PlotArea.Interior.ColorIndex = 19
                 ActiveChart.DataTable.Font.Size = 8
                 
                               
                 ActiveChart.Pictures.Insert("C:\tempfile\Sl.jpg").Select
                
               
                .PageSetup.Orientation = xlLandscape
                           .PageSetup.LeftMargin = Application.InchesToPoints(0.25)
                           .PageSetup.RightMargin = Application.InchesToPoints(0.25)
                           .PageSetup.CenterHorizontally = True
                           .PageSetup.LeftFooter = "&D"
                           .PageSetup.CenterFooter = "&P(&N)"
                           .PageSetup.RightFooter = "&A"
                           
              End With

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[bodo62]

Great!

Thanks Skip... that made the trick...

I wish you all a nice week-end.