0

I have an Excel workbook that contains several worksheets. For each worksheet I want to generate a chart based on a specified range of data. The columns for the chart data will always be the same however some worksheets will contain more rows of data than others. The code I have at the moment seems to work fine when there is only one worksheet but when I have several worksheets it is not always selecting the correct data range. I'm new to VBA so any help and advise will be appreciated. Thanks in advance! 

Dim Wb As Workbook
Dim Ws As Worksheet
Dim Cht As Chart 

Set Wb = Workbooks.Open(Filename:=Application.GetOpenFilename)

 For Each Ws In Wb.Worksheets   
    Dim MyRange1 As String
    Dim MyRange2 As String
    Dim MyRange3 As String
    Dim MyRange4 As String

    MyRange1 = Range([O1], [O1].End(xlDown)).Address
    MyRange2 = Range([Q1], [Q1].End(xlDown)).Address
    MyRange3 = Range([R1], [R1].End(xlDown)).Address
    MyRange4 = Range([S1], [S1].End(xlDown)).Address

    MyString1 = MyRange1 & "," & MyRange2
    MyString2 = MyRange3 & "," & MyRange4
    MyChtRange = MyString1 & "," & MyString2

    Set Cht = Ws.Shapes.AddChart.Chart
    Cht.SetSourceData Source:=Ws.Range(MyChtRange)
    Cht.ChartType = xlColumnStacked
 Next Ws
pnuts
  • 58,317
  • 11
  • 87
  • 139
OO_Learner
  • 73
  • 1
  • 2
  • 8
  • change `Range(...)` to `Ws.Range(...)`. Btw, using `xlDown` is not reliable. Better to use `xlUp`. See this link for more details: [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) – Dmitry Pavliv Mar 12 '14 at 19:53
  • your setting 4 ranges on one sheet and then overwriting them so that all 4 ranges arre on the last sheet – Steven Martin Mar 12 '14 at 19:54
  • @simoco changing to Ws.Range gave me a 'Method Range of worksheet failed' error. – OO_Learner Mar 12 '14 at 20:10
  • @Steven Martin I suspect you are right as I seem to be selecting the data range for the previous worksheet. How do I correct this? – OO_Learner Mar 12 '14 at 20:10
  • use this: `ws.Range(ws.Range("O1"), ws.Range("O" & rows.Count).End(xlUp)).Address` – Dmitry Pavliv Mar 12 '14 at 20:11

1 Answers1

1

after 

For Each Ws In Wb.Worksheets

add one line

Set Ws = ActiveSheet
Steven Martin
  • 3,150
  • 1
  • 20
  • 27