Copy and Paste into new Sheet

[jaysh]

Just want to thank everyone for all the support you have given me in the past. I got another question here.


I'm trying to find a value in a column, copy the entire row, and paste it onto the next sheet.

For instance, if the term "Credit" is found in column B 10 times, I want all 10 rows copied to the next sheet.


Here is the code I have so far. (i'm a noob)


Sub FindCredit ()


Dim Credit As Range
Application.ScreenUpdating = False
Set Credit = Range("B:B").Find(what:="Credited")
Do Until Credit Is Nothing
Credit.EntireRow.Cut
Set Credit = Range("B:B").FindNext


Loop


End Sub

I know i'm missing a piece of code right after the Set Credit = Range("B:B").FindNext and was hoping you can tell me what the paste command would be.

End Sub

 

[stnkynts]

Hello jaysh,

Seems like you are on the right track doing it that way. Only problem I see is that i dont think Credit will ever be nothing. It will just keep looping back on itself. A way to stop this would be something like this (untested)

Sub FindCredit ()
 
Dim Credit As Range
Application.ScreenUpdating = False
Set Credit = Range("B:B").Find(what:="Credited")
startaddress = Credit.Address
Do 
Credit.EntireRow.Cut
Set Credit = Range("B:B").FindNext
 
Loop While Not Credit Is Nothing And startaddress <> Credit.Address
 
End Sub

This should pick it up on its way back around. Another way to do it would be to use a For/Next loop. If you want i can help you create one of those.

 

[jaysh]

Tried playing with the code some more. Got a little bit farther. But still isn't doing what I want it to.

Sub FindCredit()

Dim Credit As Range
Application.ScreenUpdating = False
Set Credit = Range("B:B").Find(what:="Credited")
Do Until Credit Is Nothing
Credit.EntireRow.Cut
Set Credit = Range("B:B").FindNext
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A:AZ")

End
Loop


On the range, i'm not sure how many rows there will be. I know it will go to column AZ though.

 

[stnkynts]

Ok here are two different ways of doing it. One is the For/Next loop i was talking about and the other is the .Find method you were trying to use. Both have been tested and work. I assumed that the search is taking place in Sheet1 and the output is in Sheet2. Minor changes can edit this if neccessary:

For/Next Method:

Option Explicit
Sub FindCredited()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2")
Dim lastrow As Long, icell As Long
 
lastrow = ws1.Range("B" & Rows.Count).End(xlUp).Row
 
For icell = 1 To lastrow
    If ws1.Range("B" & icell).Value = "Credited" Then
        ws1.Range("B" & icell).EntireRow.Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next icell
 
End Sub

.FindNext Method:

Option Explicit
Sub FindCredited2()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2")
Dim Credit As Range
Dim startaddress As Variant
 
Application.ScreenUpdating = False
Set Credit = ws1.Range("B:B").Find(what:="Credited")
 
If Not Credit Is Nothing Then
    startaddress = Credit.Address
        Do
            Credit.EntireRow.Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Set Credit = ws1.Range("B:B").FindNext(Credit)
        Loop While Not Credit Is Nothing And startaddress <> Credit.Address
End If
 
Application.ScreenUpdating = True
 
End Sub

Let me know.

 

[jaysh]

Thank you so much! Way helpful!