PDA

View Full Version : Solved: copy visible cells



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

GTO
08-29-2009, 08:34 PM
Hi Zach,

Not super well tested, but seems to work:


Option Explicit

Sub Copytest1()
Dim spLastRw As Long
Dim iLastRow As Long
Dim lLastDataRow As Long

spLastRw = ThisWorkbook.Worksheets("Volume").Cells(Rows.Count, "B").End(xlUp).Row + 1

With ThisWorkbook.Worksheets("data")
iLastRow = .Cells(Rows.Count, "b").End(xlUp).Row
.AutoFilterMode = False
.Range("a5:d5").AutoFilter
.Range("a5:d5").AutoFilter Field:=1, Criteria1:="="
.Range("a5:d5").AutoFilter Field:=4, Criteria1:="S"

'// I couldn't think of a brighter way to do this, so to figure how many rows //
'// need inserted... Get a count of visible cells in our range and divide //
'// this by how many columns (in the App's Ver) //
lLastDataRow = (.Rows("6:" & iLastRow).SpecialCells(xlCellTypeVisible).Count _
/ Columns.Count) + 5

ThisWorkbook.Worksheets("Volume").Rows("6:" & lLastDataRow).Insert xlShiftDown

.Rows("6:" & iLastRow).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Volume").Range("A6")

.AutoFilterMode = False
End With
End Sub


Hope that helps,

Mark

vzachin
08-31-2009, 08:12 AM
hi Mark,

thanks for the code. i understand what you're doing but i don't understand how/why it works

thanks again
zach