CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

copying one row to another based on value

I am trying to copy my data from one sheet to another depending on the column value. I had the below code running but due to some reason, I could see the column pasted from 52 row.

I wanted to have them pasted in the 2row of the sheet everytime. Could someone help where I am wring with the code ? 

Sub list()
Dim cell As Range
Dim nextrow As Long
Application.ScreenUpdating = False
For Each cell In Sheets("BW").Range("T5:T" & Sheets("BW").Cells(Sheets("BW").Rows.Count, "T").End(xlUp).Row)
If cell.Value = "1" Then
nextrow = Sheets("PSW").Cells(Sheets("PSW").Rows.Count, "T").End(xlUp).Row
Rows(cell.Row).Copy Destination:=Sheets("PSW").Range("A" & nextrow + 1)
End If
Next
Application.ScreenUpdating = True
End Sub

I tried everytime checking the column T, if it is one in my sheet BW , I paste the complete row to the sheet PSW It is everytime getting pasted from 52 row. Pleasehelp me to resolve this

Upvotes: 0

Views: 47

Answers (1)

Srikanta Gouda
Srikanta Gouda

Reputation: 521

Can you check your PSW sheet whether T column has existing data.

or You can use CountA for better clarity than using Cells(Sheets("BW").Rows.Count, "T") for getting the row number.

Sub list()
Dim cell As Range
Dim nextrow As Long
Dim a As Double

Application.ScreenUpdating = False
a = Application.WorksheetFunction.CountA(Sheets("BW").Range("T:T"))
For Each cell In Sheets("BW").Range("T5:T" & a)
If cell.Value = "1" Then
nextrow = Application.WorksheetFunction.CountA(Sheets("PSW").Range("T:T"))
Rows(cell.Row).Copy Destination:=Sheets("PSW").Range("A" & nextrow + 1)
End If

Next
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions