vzachin
08-29-2009, 07:26 PM
hi,
i have the following code which does a filter in sheet "data" and copies the visible cells and pastes to the last row in sheet "volume" . what i want to do instead is to insert paste in row 6 of sheet "volume" and shift the data down.
sub Copytest1()
With Sheets("Volume")
spLastRw = .Cells(Rows.Count, "b").End(xlUp).Row
spLastRw = spLastRw + 1
End With
With Sheets("data")
iLastRow = .Cells(Rows.Count, "b").End(xlUp).Row
If .AutoFilterMode Then
.AutoFilterMode = False
.Range("a5:d5").AutoFilter
.Range("a5:d5").AutoFilter Field:=1, Criteria1:="="
.Range("a5:d5").AutoFilter Field:=4, Criteria1:="S"
Else
.Range("a5:d5").AutoFilter
.Range("a5:d5").AutoFilter Field:=1, Criteria1:="="
.Range("a5:d5").AutoFilter Field:=4, Criteria1:="S"
End If
.Rows("6:" & iLastRow).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Volume").Range("A" & spLastRw)
.AutoFilterMode = False
End With
End Sub
i tried the following but it doesn't paste anything
.Rows("6:" & iLastRow).SpecialCells(xlCellTypeVisible).Copy
Sheets("Volume").Select
Range("A6").Select
Selection.Insert Shift:=xlDown
thanks
zach
i have the following code which does a filter in sheet "data" and copies the visible cells and pastes to the last row in sheet "volume" . what i want to do instead is to insert paste in row 6 of sheet "volume" and shift the data down.
sub Copytest1()
With Sheets("Volume")
spLastRw = .Cells(Rows.Count, "b").End(xlUp).Row
spLastRw = spLastRw + 1
End With
With Sheets("data")
iLastRow = .Cells(Rows.Count, "b").End(xlUp).Row
If .AutoFilterMode Then
.AutoFilterMode = False
.Range("a5:d5").AutoFilter
.Range("a5:d5").AutoFilter Field:=1, Criteria1:="="
.Range("a5:d5").AutoFilter Field:=4, Criteria1:="S"
Else
.Range("a5:d5").AutoFilter
.Range("a5:d5").AutoFilter Field:=1, Criteria1:="="
.Range("a5:d5").AutoFilter Field:=4, Criteria1:="S"
End If
.Rows("6:" & iLastRow).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Volume").Range("A" & spLastRw)
.AutoFilterMode = False
End With
End Sub
i tried the following but it doesn't paste anything
.Rows("6:" & iLastRow).SpecialCells(xlCellTypeVisible).Copy
Sheets("Volume").Select
Range("A6").Select
Selection.Insert Shift:=xlDown
thanks
zach