从当前工作簿中，我想在源工作簿中打开一张工作表，关闭可能存在的任何筛选，按一列排序，然后按另一列排序。我得到了1004和其他错误。这需要在2003年运行。任何帮助都将不胜感激！
Dim WB As Workbook, WasWBOpen As Boolean, srcfile As String, srcpath As String,
onecol as integer, twocol as integer, thrcol as integer
srcpath = "blahblah"
srcfile = "blah.xls"
On Error Resume Next
Set WB = Workbooks(srcfile)
WasWBOpen = True
On Error GoTo 0
If WB Is Nothing Then
Set WB = Workbooks.Open(srcpath & srcfile, UpdateLinks:=False)
WasWBOpen = False
End If
'code before this opens source wkbook
lstrow = Worksheets("Sheet1").UsedRange.Row - 1 + Worksheets("Sheet1").UsedRange.Rows.Count
lstcol = Worksheets("Sheet1").UsedRange.Column - 1 + Worksheets("Sheet1").UsedRange.Columns.Count
onecol=3
twocol=5
thrcol=8
With WB.Sheets("Sheet1")
.AutoFilterMode = False
.Range("1:1").AutoFilter
'Here's where error occurs--
.Range(Cells(1, 1), Cells(lstrow, lstcol)).Sort _
Key1:=Columns(onecol), Order1:=xlAscending, _
Key2:=Columns(twocol), Order2:=xlAscending, _
Key3:=Columns(thrcol), Order3:=xlAscending, Header:=xlYes
End With
If WasWBOpen = False Then
WB.Close
End If
.Range(Cells(1, 1), Cells(lstrow, lstcol)).Sort _
最好写成：
.Range(.Cells(1, 1), .Cells(lstrow, lstcol)).Sort _
我唯一可以尝试的是，您的选择可能不包括第3、5或8列。发生错误之前，lstrow和lstcol的值是多少？
根据我的经验，您只能对活动工作表进行排序。尝试在WB.Sheets("Sheet1")之后添加.Activate。
有关最后排序的信息存储在工作表中。我有时怀疑这是问题所在，而不是要排序的工作表没有激活。但.Activate一直对我有效，所以我从未进一步调查过。
额外信息
我曾以为是Sort产生了错误，但实际上是AutoFilter。
我可以通过将第1行留空来生成1004错误。
AutoFilter语句的作用是什么？对于AutoFilterMode = False，我希望它返回Nothing。为什么不删除此声明？
我还关心你正在排序的范围。您要减去顶部未使用的行数和左侧未使用的列数，以计算lstrow和lscol，但在排序中包括这些未使用的行将和列。结果是底部的行和右侧的列将不会排序。
如果顶部没有任何未使用的行，左侧没有未使用的列，这无关紧要，但您需要决定要排序的范围。
额外信息2
这一部分是在我发现了破坏原始代码的第四种方法后添加的。以下代码似乎是防炸弹的。
Option Explicit
Sub TestSort2()
Dim WB As Workbook, WasWBOpen As Boolean, srcfile As String, srcpath As String
Dim onecol As Integer, twocol As Integer, thrcol As Integer
' Undeclared or new variables
Dim InxWB As Long
Dim lstrow As Long
Dim lstcol As Long
Dim srcpathfile As String
' Report the name of the active workbook
Debug.Print "Original active workbook " & ActiveWorkbook.Name
' I created two workbooks named "Failed sort 1.xls" and "Failed sort 2.xls".
' Both are in the same directory. "Failed sort 1.xls" contains this macro.
' "Failed sort 2.xls" contains the data.
srcpath = Application.ActiveWorkbook.Path
srcfile = "Failed sort 2.xls"
srcpathfile = srcpath & "" & srcfile
WasWBOpen = False
' Check the open workbook for srcfile
For InxWB = 1 To Workbooks.Count
If Workbooks(InxWB).Name = srcfile Then
' Required workbook already open
Set WB = Workbooks(InxWB)
WB.Activate ' Activate it
WasWBOpen = True
Exit For
End If
Next
If Not WasWBOpen Then
' Files was not open
If Dir(srcpathfile) <> "" Then
' File exists
' Do you need UpdateLinks:=False? If there are links
' with the sort be affected if they are not updated?
Set WB = Workbooks.Open(srcpathfile, UpdateLinks:=False)
Else
' File does not exist
Call MsgBox(srcpathfile & " does not exist", vbOKOnly)
Exit Sub
End If
End If
' WB is now the active workbook whether it was open before or not
Debug.Print "Final active workbook " & ActiveWorkbook.Name ' Confirm
With Sheets("Sheet1")
.Activate
.AutoFilterMode = False
' Get the last used row and cell of the worksheet
lstrow = Cells.SpecialCells(xlCellTypeLastCell).Row
lstcol = Cells.SpecialCells(xlCellTypeLastCell).Column
onecol = 3
twocol = 5
thrcol = 8
If onecol > lstcol Or twocol > lstcol Or thrcol > lstcol Then
Call MsgBox("The sort range does include the sort columns", vbOKOnly)
If Not WasWBOpen Then
Close
End If
Exit Sub
End If
Range(Cells(1, 1), Cells(lstrow, lstcol)).Sort _
Key1:=Columns(onecol), Order1:=xlAscending, _
Key2:=Columns(twocol), Order2:=xlAscending, _
Key3:=Columns(thrcol), Order3:=xlAscending, Header:=xlYes
End With
If Not WasWBOpen Then
Close
End If
结束子