CODEWITHSUNDEEP
vbaexcel
OO_Learner
OO_Learner

Reputation: 73

Selecting a dynamic range of data for a Chart

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

Upvotes: 0

Views: 139

Answers (1)

Steven Martin
Steven Martin

Reputation: 3272

after 

For Each Ws In Wb.Worksheets

add one line

Set Ws = ActiveSheet

Upvotes: 1

Related Questions