如何在另一个工作表上创建图表之前对一个工作表上的数据进行排序？

问题描述

VBA：我正在尝试根据单独工作表中包含的数据填充主工作表上的条形图。但是，在填充图表之前，我需要让vba按降序对数据进行排序。

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.

当我尝试包含代码对数据进行排序时，我得到运行时错误91.理想情况下，我想在生成图表之前对N列中的数据进行排序。谢谢。

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

推荐答案

我尝试使用您的代码进行测试并得到相同的错误。

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

输出：

问候

Deepak

这篇关于如何在另一个工作表上创建图表之前对一个工作表上的数据进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！