|
本帖最后由 wling1228 于 2014-10-16 16:04 编辑
希望达到的效果是:将“逾期明细表0925”中的数据复制到后面的“today”sheet,并且保留最大日期对应的“类型”栏的值,其它“类型”中的值都删除。
写了代码如下,但是觉得运行太慢,请问还有没有更高效的方法?谢谢!
Sub copy()
Dim x As Integer
Dim D As Date
'Dim t As String
'D = Year(Now() - 1) & "/" & Month(Now() - 1) & "/" & Day(Now() - 1)
Sheets("逾期明细表0925").Activate
x = Cells(Rows.Count, "a").End(xlUp).Row
ActiveSheet.Range("A2:Y" & x).Select
Selection.copy
Sheets("today").Activate
ActiveSheet.Range("a2").Select
ActiveSheet.Paste
D = Application.WorksheetFunction.Max(Range("l3:l" & x))
ActiveSheet.Range("A2:AD" & x).AutoFilter Field:=12, Criteria1:="<>" & D, Operator:=xlFilterValues
With Range("y3:y" & x)
.SpecialCells(xlCellTypeVisible).Value = ""
.EntireRow.Hidden = False
End With
End Sub
- Sub copy()
- Dim x As Integer
- Dim D As Date
- Dim Sh As Worksheet
- Set Sh = Sheets("Today")
- With Sheets("逾期明细表0925")
- x = .Cells(.Rows.Count, "a").End(xlUp).Row
- .Range("A2:Y" & x).copy Sh.Range("a2")
- End With
- D = Application.WorksheetFunction.Max(Sh.Range("l3:l" & x))
- Sh.Range("A2:AD" & x).AutoFilter Field:=12, Criteria1:="<>" & D, Operator:=xlFilterValues
- With Sh.Range("y3:y" & x)
- .SpecialCells(xlCellTypeVisible).Value = ""
- .EntireRow.Hidden = False
- End With
- End Sub
复制代码
|
|