CODEWITHSUNDEEP
vbaexcel
Robillard
Robillard

Reputation: 117

Pasting row from one sheet to the other to first blank row

I have seen some examples but they have been using .Select and .Activate. I am trying to learn how to not use those anymore because everyone says you should try to stay away from them.

I want to take a row, then copy it to the first blank row on the other sheet. I was close but it just isn't working. 

UsdRws = Range("A" & Rows.Count).End(xlUp).Row


With Sheets("Totals by Department")
    .Range("A1:Z" & UsdRws).autofilter Field:=1, Criteria1:="1450"
    .Range("A2:Z" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.COPY
End With


Set NextRow = Range("A" & Sheets(2).UsedRange.Rows.Count + 1)


Sheets(2).NextRow.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Set NextRow = Nothing

The first part copies perfectly, I really just need help pasting it over on the other sheet. I will also take other recommendations for cleaning the code up. Like I said I am trying to learn to write better. The second part is messy because I have been adding and editing it but now I am lost.

Upvotes: 0

Views: 58

Answers (2)

jsotola
jsotola

Reputation: 2278

' UsdRws is equal the last used row on whichever sheet is active at the moment that this code runs 

UsdRws = Range("A" & Rows.Count).End(xlUp).Row

' this code properly references ranges on a specific worksheet, regardless of which worksheet is active

With Sheets("Totals by Department")
    .Range("A1:Z" & UsdRws).autofilter Field:=1, Criteria1:="1450"
    .Range("A2:Z" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.COPY
End With

' NextRow is reference to a cell on whichever sheet is active at the moment that this code runs
' but the row referenced is same as the first emply cell on Sheets(2) 

Set NextRow = Range("A" & Sheets(2).UsedRange.Rows.Count + 1)

' NextRow is already a range .... so it should be NextRow.PasteSpecial ......

Sheets(2).NextRow.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Set NextRow = Nothing

this may be what you want

With Sheets("Totals by Department")
    UsdRws = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1:Z" & UsdRws).autofilter Field:=1, Criteria1:="1450"
    .Range("A2:Z" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.COPY
End With

Set NextRow = Sheets(2).Range("A" & Sheets(2).UsedRange.Rows.Count + 1)

NextRow.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Set NextRow = Nothing

Upvotes: 0

Smith
Smith

Reputation: 98

Your "NextRow" object is a Range object, but you are calling it as if it were a method or property of Sheets(2).

Try removing the Sheets(2). and just start with Next Row.

Set NextRow = Sheets(2).Range("A" & Sheets(2).UsedRange.Rows.Count + 1)
NextRow.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Upvotes: 2

Related Questions