|
楼主 |
发表于 2017-8-17 13:15
|
显示全部楼层
本帖最后由 sunshinecrx 于 2017-8-17 13:18 编辑
Private Sub CommandButton2_Click()
Dim RowNum%, ColumnNum%
RowNum = Range("A65536").End(xlUp).row
ColumnNum = Range("A1").CurrentRegion.Columns.Count
Range(Cells(1, 1), Cells(RowNum, ColumnNum)).Copy Destination:=Range("T1")
ActiveWorkbook.Worksheets("工作底稿").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("工作底稿").Sort.SortFields.Add _
Key:=Range("T2:T31"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
CustomOrder:="上 海,北 京,广 东,江 苏,山 东,浙 江", _
DataOption:=xlSortNormal
'应用新的排序字段进行排序
With ActiveWorkbook.Worksheets("工作底稿").Sort
.SetRange Range("T1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Dim datatable()
ReDim datatable(1 To RowNum, 1 To ColumnNum)
datatable = Range("T1").CurrentRegion
Sheets("得分排名").Range("A1").Resize(ColumnNum, RowNum) = Application.WorksheetFunction.Transpose(datatable)
End Sub
-----------
以上代码在SHEET1的VBE环境下可以运行
但是将其中的一段代码放在SHEET2中的VBE环境下,就报错了。
Dim datatable()
ReDim datatable(1 To RowNum, 1 To ColumnNum)
datatable = Sheets(1).Range("T1").CurrentRegion
Range("A1").Resize(ColumnNum, RowNum) = Application.WorksheetFunction.Transpose(datatable)
|
|