0

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
user3266585
  • 1
  • 1
  • 1
    Would something like [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) help? – Siddharth Rout Feb 03 '14 at 16:22
  • I see lots of code and not a specific question. What doesn't work? Have you tried to fix it yourself? If you look at your comments you should be able to easily figure out where you need to make changes... –  Feb 03 '14 at 16:26

1 Answers1

2

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.

user1759942
  • 1,322
  • 4
  • 14
  • 33
  • 1
    + 1 `Some people would tell you my way is wrong` Why would you say that :) `.Find` is the best way to find the last row. You may want to see [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba). Also you can change `WSb.Cells(lastrow+1, 1).pastespecial` to `WSb.rows(lastrow+1).pastespecial....` – Siddharth Rout Feb 03 '14 at 16:39
  • musta been you I got that from in the first place then :) I've only seen that way used once, the post where I got it from. I've never seen it anywhere else, EVERYONE else that I've seen uses some combo of `.end(xlUp)` so that's why I include the 'disclaimer' type statement :P – user1759942 Feb 03 '14 at 18:40
  • `musta been you I got that from in the first place then` Not possible :) – Siddharth Rout Feb 03 '14 at 18:42
  • why do you say that? :/ – user1759942 Feb 03 '14 at 18:48
  • Becuase like I mentioned in my first comment, `.Find` IS the best way. I even upvoted your answer. Not sure why do you have a ":/" sarcastic smiley there? – Siddharth Rout Feb 03 '14 at 18:50
  • haha I meant it must have been you I got my method of .find from XD and it was supposed to be a confused smiley.. I wasnt paying attention, i meant to put ":S" there lol – user1759942 Feb 03 '14 at 19:00