在粘贴 Excel VBA 之前对范围进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15017717/
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 Range before pasting Excel VBA
提问by Wilz
I'm using the following code to copy a particular range from a Workbookto another Workbook, its working fine.
我正在使用以下代码将特定范围从 a 复制Workbook到 another Workbook，它工作正常。
But now i need to sort the Rangein ascending order just before pasting to the destination sheet without changing the source. Please help.
但是现在我需要Range在粘贴到目标工作表之前按升序排序而不更改源。请帮忙。
With Workbooks(strExcelFile).Sheets(strSheetName)
.Range(strRange).Copy
End With
ActiveSheet.Range(strDestCell).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
回答by Joe
Take advantage of the fact that once you paste, your newly pasted range will be selected; then you can use SELECTION.
利用粘贴后，新粘贴的范围将被选中的事实；那么你可以使用选择。
Public Sub test()
Range("A1:A8").Copy
ActiveSheet.Range("B1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Sort key1:=Range("B1")
End Sub
That test example will work in any excel file with some data in A1-A8. B1 in both places can be replaced with strDestCell and A1:A8 with strRange for your eventual subroutine.
该测试示例适用于任何包含 A1-A8 中某些数据的 Excel 文件。两个地方的 B1 都可以用 strDestCell 替换，而 A1:A8 用 strRange 替换为您最终的子程序。
回答by brettdj
You should try to avoid the redundant Selectwhen working with ranges. You can work more cleanly using worksheetsand rangesas below, which is easily adaptable accross workbooks as per your question
Select在使用范围时，您应该尽量避免冗余。您可以使用worksheets和更干净地工作ranges，如下所示，根据您的问题，它可以轻松适应工作簿
code
代码
Sub ReCut()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Set ws1 = ThisWorkbook.Sheets(1)
Set ws2 = ThisWorkbook.Sheets(2)
Set rng1 = ws1.Range("A1:A10")
With ws2.[b1].Resize(rng1.Rows.Count, 1)
.Value = rng1.Value
.Sort ws2.[b1]
End With
End Sub
