vba 在不选择该工作表的情况下对另一工作表中的列进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19011777/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sort a Column in Another Sheet Without Selecting That Sheet
提问by Pike7893
Is it possible to sort columns in another sheet without selecting that sheet? The problem is while I am running this code, I want this sheet to be hidden and I do not want it to flash over to it when I need to sort the table. Here is my code... This works, but it does obviously select the sheet and shows you the other sheet. Maybe something with 'make active sheet' would work can you do that then say 'make active cell'. I am not sure. Thanks guys.
是否可以在不选择该工作表的情况下对另一张工作表中的列进行排序？问题是当我运行这段代码时，我希望这个工作表被隐藏，我不希望它在我需要对表格进行排序时闪到它上面。这是我的代码......这有效，但它显然选择了工作表并向您显示另一张工作表。也许“制作活动表”的东西会起作用，你可以这样做然后说“制作活动单元格”。我不确定。谢谢你们。
Application.Worksheets("RawDataLines").Select
Application.Worksheets("RawDataLines").Range("Q5").Select
Application.Worksheets("RawDataLines").Range("A4:R1007").Sort Key1:=Range("Q5"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Worksheets("RawDataLines").Range("A5").Select
Application.Worksheets("RawDataLines").Range("A4:R1007").Sort Key1:=Range("A5"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
回答by Tim Williams
This:
这个：
Application.Worksheets("RawDataLines").Select
Application.Worksheets("RawDataLines").Range("Q5").Select
Application.Worksheets("RawDataLines").Range("A4:R1007").Sort ...
Could be this:
可能是这样的：
With Application.Worksheets("RawDataLines")
.Range("A4:R1007").Sort Key1:= .Range("A5")'...
End With
回答by engineersmnky
Add ScreenUpdating
添加 ScreenUpdating
Application.ScreenUpdating = False
#Your Code
Application.ScreenUpdating = True
Just make sure that you set the select back to the sheet you want it on prior to setting ScreenUpdating = True
只需确保在设置之前将选择设置回您想要的工作表 ScreenUpdating = True
回答by Bertram G. Liyanage
Here the problem comes with the Key1 argumentsof VBA sort method. Even though we explicitly pass range with relevant sheet, when it comes to Key1:=Range(…), VBA automatically take ActiveSheet.Range(…).
这里的问题来自于Key1 argumentsVBA 排序方法。即使我们明确地通过相关工作表传递范围，当涉及到 时Key1:=Range(…)，VBA 会自动采用ActiveSheet.Range(…).
If We have selected some other sheet instead of the sheets of sort data, VBA shows runtime error.
如果我们选择了其他工作表而不是排序数据工作表，VBA 会显示运行时错误。
To fix this problem change the code as Key1:=Your_Sheet_With_Data.Range(Your_Range). For Example, above referred code can be fixed changing the code as
要解决此问题，请将代码更改为Key1:=Your_Sheet_With_Data.Range(Your_Range). 例如，上面提到的代码可以固定更改代码为
Key1:=Worksheets("RawDataLines").Range("Q5")
