Selecting cells in different columns for charts

[bodo62]

Hi.

I've got following code in which I want to extract information from a worksheet for a specific chart:

I want to get info from D1 to D & last filled row and then I want get info from J1 to N & last filled row, but I also get columns E,F,G,H and I

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 2) = "SA" Then
    Charts.Add after:=ws
        
    With ActiveChart
            
              lRow = ws.Range("D65536").End(xlUp).Row
              .SetSourceData Source:=ws.Range("D1:D" & lRow, "J1:N" & lRow), PlotBy:=xlColumns  '<<==== should get D1:D & lRow and J1:N & lRow
                  
              .Location Where:=xlLocationAsNewSheet, Name:=ws.Name & "_OL"
          
              .HasTitle = True
              .ChartTitle.Characters.Text = "ORDER LINES " & ws.Range("A2")
              .Axes(xlCategory, xlPrimary).HasTitle = False
              .Axes(xlValue, xlPrimary).HasTitle = True
              .HasLegend = False
              .HasDataTable = True
              .DataTable.ShowLegendKey = True
                
            ActiveChart.Pictures.Insert("C:\tempfile\ScaloggaPytte.gif").Select
              .ChartType = xlLineMarkers
                ActiveChart.PageSetup.LeftFooter = "&D"
                         .PageSetup.CenterFooter = "&P(&N)"
                         .PageSetup.RightFooter = "&A"
                        
     End With
End If

I guess I am doing something wrong, but I just don't see it..

Thanks in advance for any help

 

[Molby]

Yep, it's your source data that's the problem, you missed out the comma between D and J, use:

.SetSourceData Source:=ws.Range("D1:D" & lRow & "," & "J1:N" & lRow)

 

[Molby]

You're missing a comma

.SetSourceData Source:=ws.Range("D1:D" & lRow & "," & "J1:N" & lRow)

 

[SkipVought]

Hi,

.SetSourceData Source:=ws.Range("A1:A" & lrow [highlight]& ",[/highlight]D1:E" & lrow), PlotBy:=xlColumns

missplaced comma and concatenation.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[bodo62]

Yes, of course... thanks for all replies. I guess it's time to buy myself a new pair glasses...