CODEWITHSUNDEEP
vbaexcel
user3266585
user3266585

Reputation: 1

Copying a entire row and pasting it into the next empty row in a new sheet

I've searched around trying to find the right answer and most answers are geared towards copying a whole range.

I'm trying to find a specific value, copy that entire row, and paste it into a new worksheet. From there, this whole process should loop and keep adding each additional row into the second worksheet without overwriting what was previously entered. Currently the macro is overwriting the previously found row.

Here is my code:

' Search for segment data to add
Sub SegSearch()
Dim I As Integer
Dim Output As Integer
Dim KeepRunning As Boolean
Dim OtherCondition As String
Dim finalval As Long

' Declare Search Variable
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

' Declare Worksheet Variables
Dim WSa As Worksheet
Dim WSb As Worksheet

 ' Define WSa/WSb as respective worksheets
Set WSa = Sheets("STARS Formatted")
Set WSb = Sheets("memo_db")

' Selects "STARS Formatted" sheet for search
Sheets("STARS Formatted").Select

While KeepRunning = False

' User must enter Segment Value
LSearchValue = Application.InputBox("Please enter a Segment to search for.", "Enter  Segment")

' User enters null value, exit sub
If LSearchValue = "" Then
    Destroy = True
    MsgBox ("No Value entered")
End If

' User selects "cancel", exit sub
 If LSearchValue = "False" Then
    MsgBox ("User Canceled")
    Exit Sub
 End If

' ensures if user enters lowercase value will be Uppercase to handle proper search
LSearchValue = UCase(LSearchValue)

' Defines first condition to search for in report
OtherCondition = "Segment Total"

' determines last row in For Loop
finalval = Cells(Rows.Count, "C").End(xlUp).Row


For I = 2 To finalval

 If CStr(Cells(I, 3).Value) = OtherCondition And CStr(Cells(I, 8).Value) = LSearchValue Then

 ' Start search in row 2
     LSearchRow = I

' Start copying data to row 2 in Sheet2 (row counter variable)
     LCopyToRow = LSearchRow

    'Select row in "STARS Fastdata" to copy
     WSa.Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy


     ' Paste row into memo_db in next row
     WSb.Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).End(xlUp).Offset(1, 0).PasteSpecial


     ' Move copy counter to next row
     LCopyToRow = LCopyToRow + 1

End If

Next I

    Output = MsgBox("Do you want to add another segment?", vbYesNo, "Add Another Segment")

If Output = 6 Then
    KeepRunning = False

Else
    KeepRunning = True

End If

Wend

End Sub

Upvotes: 0

Views: 1038

Answers (1)

user1759942
user1759942

Reputation: 1350

what i would do is instead of:

WSb.Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).End(xlUp).Offset(1, 0).PasteSpecial

i would do this in 2 steps:

lastrow = WSb.Cells.find("*", [A1], , , xlByRows, xlPrevious).Row 'will give you number of last row
WSb.Cells(lastrow+1, 1).pastespecial

i found it hard to tell exactly what was happening on the existing line, so find the number to the last row, and then use the paste with just WSb.cells(lastrow + 1, 1).pastespecial` and that'll paste into the row after the last row, in column 1.

you can also use your own way of finding the last row, some people would tell you my way is wrong, but I find it works better. its up to you.

Upvotes: 2

Related Questions