Excel vba排序宏

Question

我正在设计一个宏，它将按键复制和排序数据，然后在vba中的原始表单之前插入2列。我已经开始编写代码，它迄今为止运行良好，但是当我尝试插入2列时，它给了我一个Select method of Range class failed错误：Sheet2.Columns("A:A").Select我很困惑为什么会发生这种情况。任何帮助将不胜感激。

Sub crossUpdate() 

Dim rng1 As Range, rng2 As Range, rng1Row As Range, rng2Row As Range, Key As Range, match As Integer 


'Unhide and Unfilter columns and rows on original sheet 
Sheet1.Cells.EntireColumn.Hidden = False 
Sheet1.Cells.EntireRow.Hidden = False 
Sheet1.Cells.AutoFilter 

'Copy and paste original sheet to new temp sheet 
Sheet1.Cells.Select 
Selection.Copy 
Sheets.Add.Name = "SourceData" 
ActiveSheet.Paste 
Range("A1").Select 

'Sort temp sheet by key 
N = Cells(Rows.Count, "A").End(xlUp).row 
Set rng1 = Sheets("SourceData").Cells.Range("A2:A" & N) 
Set rng1Row = rng1.EntireRow 
rng1Row.Sort Key1:=Sheets("SourceData").Range("A1") 



'Unhide and Unfilter columns and rows on original sheet 
Sheet2.Cells.EntireColumn.Hidden = False 
Sheet2.Cells.EntireRow.Hidden = False 
Sheet2.Cells.AutoFilter 

'Update sheet sorted by key 
N = Cells(Rows.Count, "A").End(xlUp).row 
Set rng2 = Sheets("Sheet2").Cells.Range("A2:A" & N) 
Set rng2Row = rng2.EntireRow 
rng2Row.Sort Key1:=Sheets("Sheet2").Range("A1") 


Sheet2.Columns("A:A").Select 
Selection.Insert Shift:=xlToRight 
Sheets("SourceData").Columns("A:A").Select 
Selection.Insert Shift:=xlToRight 

End Sub 

Answer 1

我认为这是因为您在不同的工作表上使用选择而不是活动工作表。

一种解决方案是要做到：

Sheet2.Select 
Sheet2.Columns("A:A").Select 

另一种解决方案是不使用选择在所有（这是几乎总是更好）

Sheet2.Columns("A:A").Insert Shift:=xlToRight