Sub online()
Windows("bondCahed.xls").Activate
If ActiveSheet.FilterMode = True Then
ActiveSheet.Range("A:AH").AutoFilter Field:=21, Criteria1:=Array( _
"结清", "结清代偿", "正常"), Operator:=xlFilterValues
ActiveSheet.Range("A:AH").AutoFilter Field:=11, Criteria1:="是"
Else
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("A:AH").AutoFilter Field:=21, Criteria1:=Array( _
"结清", "结清代偿", "正常"), Operator:=xlFilterValues
ActiveSheet.Range("A:AH").AutoFilter Field:=11, Criteria1:="是"
End If
i = Cells.Find("*", , , , 1, 2).Row
Debug.Print i
Application.Union(Range(Cells(1, 1), Cells(i, 1)), Range(Cells(1, 2), Cells(i, 2)), Range(Cells(1, 8), Cells(i, 8)), Range(Cells(1, 9), Cells(i, 9)), Range(Cells(1, 12), Cells(i, 12)), Range(Cells(1, 26), Cells(i, 26)), Range(Cells(1, 31), Cells(i, 31))).Select
Selection.Copy
Windows("小微债.xlsx").Activate
With Workbooks("小微债.xlsx").Worksheets("online")
Range("A1").Select
ActiveSheet.Paste
End With
End Sub
Windows("bondCahed.xls").Activate
If ActiveSheet.FilterMode = True Then
ActiveSheet.Range("A:AH").AutoFilter Field:=21, Criteria1:=Array( _
"结清", "结清代偿", "正常"), Operator:=xlFilterValues
ActiveSheet.Range("A:AH").AutoFilter Field:=11, Criteria1:="是"
Else
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("A:AH").AutoFilter Field:=21, Criteria1:=Array( _
"结清", "结清代偿", "正常"), Operator:=xlFilterValues
ActiveSheet.Range("A:AH").AutoFilter Field:=11, Criteria1:="是"
End If
i = Cells.Find("*", , , , 1, 2).Row
Debug.Print i
Application.Union(Range(Cells(1, 1), Cells(i, 1)), Range(Cells(1, 2), Cells(i, 2)), Range(Cells(1, 8), Cells(i, 8)), Range(Cells(1, 9), Cells(i, 9)), Range(Cells(1, 12), Cells(i, 12)), Range(Cells(1, 26), Cells(i, 26)), Range(Cells(1, 31), Cells(i, 31))).Select
Selection.Copy
Windows("小微债.xlsx").Activate
With Workbooks("小微债.xlsx").Worksheets("online")
Range("A1").Select
ActiveSheet.Paste
End With
End Sub
背景:打算在online工作表中粘贴选择内容,结果仍会在当前活动工作表中粘贴,或者提示“类range的select方法无效”
原因:
上述代码中虽然打算选择online工作表,但最后运行结果是复制在当前活动的工作表,因为到” ActiveSheet.Paste“中的 ActiveSheet仍指当前活动的工作表,前面代码不改变当前活动的工作表。
修改办法:将上面标红的代码替换成下面代码
Workbooks("小微债.xlsx").Worksheets("online").ActiveSheet
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Paste