在粘贴Excel VBA之前对范围进行排序 [英] Sort a Range before pasting Excel VBA

问题描述

我正在使用以下代码将特定范围从工作簿复制到另一个工作簿，其工作

I'm using the following code to copy a particular range from a Workbook to another Workbook, its working fine.

但是现在我需要在粘贴到目的地表之前按升序排列 Range 来源。请帮助。

But now i need to sort the Range in ascending order just before pasting to the destination sheet without changing the source. Please help.

With Workbooks(strExcelFile).Sheets(strSheetName)
     .Range(strRange).Copy
End With

ActiveSheet.Range(strDestCell).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

推荐答案

利用这一事实，一旦您粘贴，您的新粘贴范围将被选中;那么你可以使用SELECTION。

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

该测试示例将在具有某些数据的任何excel文件中正常工作在A1-A8。两个地方的B1可以用strDestCell和A1：A8替换为最终的子程序的strRange。

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.

这篇关于在粘贴Excel VBA之前对范围进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！