CODEWITHSUNDEEP
excelvba
Natalia Fontiveros
Natalia Fontiveros

Reputation: 115

How to copy and paste with offset in the same sheet

I'm trying to copy and paste all the visible rows except the first row which is the headers.

This pastes in another sheet.

With ws1.Range("A1:Q" & LastRow)
    .AutoFilter Field:=6, Criteria1:="Movies"
    Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet1").Range("A1")
End with

However, I must copy the data in the same sheet, and my code errors out.

With ws1.Range("A1:Q" & LastRow)
    .AutoFilter Field:=6, Criteria1:="Movies"
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws1.Range("A" & LastRow + 1)
end with

I tried the same code where I assign the complete range.

With ws1.Range("A1:Q" & LastRow)
    .AutoFilter Field:=6, Criteria1:="Movies"
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws1.Range("A" & LastRow + 1 & ":Q" & LastRow + LastRow2)
end with

enter image description here

Upvotes: 0

Views: 107

Answers (1)

Tim Williams
Tim Williams

Reputation: 166755

FYI you use (effectively)

ws1.Range("A1:Q" & LastRow).Offset(1).SpecialCells(xlCellTypeVisible).Copy

so that means you're including the row below LastRow when copying, and you're also trying to paste to that same row...

You can avoid that using Resize() to exclude that last row from the Copy

With ws1.Range("A1:Q" & LastRow)
    .AutoFilter Field:=6, Criteria1:="Movies"
    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy _
                                   Destination:=ws1.Range("A" & LastRow + 1)
End With

Upvotes: 2

Related Questions