none
How do I sort data on one worksheet before creating a chart on a different sheet? RRS feed

  • Question

  • VBA: I'm trying to populate bar charts on a primary sheet based on data that is contained on a separate sheet. However, I need to get vba to sort the data in descending order before populating the chart.

    When I try to include a code to sort the data, I get the run time error 91. Ideally I would like to sort the data in column N before generating the chart. Thanks.

    Sub NewIssues2014()
    
    Dim sht As Worksheet
    Dim dashboard As Worksheet
    
    Set sht = Worksheets("New Issues League Table")
    ArrangerLastRow = sht.Cells(Rows.Count, 12).End(xlUp).Row
    YearLastRow = sht.Cells(Rows.Count, 14).End(xlUp).Row
    
    Sheets("New Issues League Table").Select
    Range("M1:S1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("New Issues League Table").AutoFilter.Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("New Issues League Table").AutoFilter.Sort.SortFields _
        .Add Key:=Range("N1"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("New Issues League Table").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    With dashboard
    dashboard.Shapes.AddChart.Select
    ActiveChart.ChartType = xlBarStacked
    ActiveChart.SetSourceData Source:=sht.Range("L2:L" & ArrangerLastRow & ",N2:N" & YearLastRow), PlotBy:=xlColumns
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
    ActiveChart.Axes(xlCategory).Crosses = xlMaximum
    ActiveChart.HasTitle = True
    ActiveChart.HasLegend = False
    ActiveChart.ChartTitle.Text = "2014 New Issue Deals"
    End With
    
    End Sub
    Tuesday, April 24, 2018 9:00 PM

All replies

  • Hi nicolechj,

    I try to make a test with your code and got the same error.

    I suggest you to do not apply "auto filter" and "sort" together.

    Try to modify your code like below.

    Sub dem0()
    With ThisWorkbook.Sheets("demo")
        .Range("A1").AutoFilter Field:=1, Criteria1:="<>"
    
        .Range("A1").CurrentRegion.Sort Key1:=.Range("A1"), Order1:=xlDescending, _
            Header:=xlYes, OrderCustom:=1, DataOption1:=xlSortNormal
    End With
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 25, 2018 7:15 AM
    Moderator
  • Hi nicolechj,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 3, 2018 9:33 AM
    Moderator