Confusing Macro and references....

[ween_boy]

I was trying to create this effect: Enter a value (in this example it was "OKCGALTA") and click a button to run a macro to search a excel sheet on a network drive (P:xxx\abc.xls) for all of the values, paste only the rows in which the value is.

Currently, it only references the current sheet (not an external sheet) and I have to go into the macro to enter the value for it to look for (not being able to type "OKCGALTA" in cell A1, for example to make it user friendly)

Any help would be much appreciated.

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

LSearchRow = 2

LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

If Range("B" & CStr(LSearchRow)).Value = "OKCGALTA" Then

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

LCopyToRow = LCopyToRow + 1

Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

 

[BrianB]

something like this ...

http://www.mrexcel.com/board2/viewtopic.php?t=150953&highlight=

 

[ween_boy]

It appears...

... that there was somebody who wrote that they altered it to copy the rows of data, but never put the code on here. Did you have it?

 

[BrianB]

Replace the section below :-

            '------------------------------------------
            '- alternative method
            '- copy/paste data to summary
            Do
                rg = "A" & FromRow & ":Z" & FromRow ' amend as needed
                FromSheet.Range(rg).Copy _
                    Destination:=ToSheet.Range("A" & ToRow)
                ToRow = ToRow + 1
                Set FoundCell = .FindNext(FoundCell)
            Loop While Not FoundCell Is Nothing And _
                FoundCell.Address <> FirstAddress
            '------------------------------------------