VBA; find string and copy

**Bill Rudd** · 01-31-2008, 03:23 AM

It's nice to meet you,

My name is Bill Rudd, I'm new to VBA but would like to finish my first project.

I would like to call up a find box, then be able to search several worksheets for a string, to copy and paste the related row to a new worksheet.

I've gotten as far as being able to search for a string and get the row pasted, as long as the string is defined in my macro. Is there a way though to be able to call up a find box first though, and search for the input variable? (alternatively, I can only imagine a separate piece of code for each variable, of which there are many, and I am actually trying to save time...

)

Is there someone that can point me in the right direction, I would be very obliged.

Yours Truly,
Bill

**royUK** · 01-31-2008, 03:44 AM

You can use an InputBox to get the string

Option Explicit

Sub findString()
    Dim sFind  As String

    sFind = Application.InputBox("Enter the search string", "Search...")
    If sFind > "" Then
        'your code
    Else: MsgBox "You did not enter anything"
        Exit Sub
    End If
End Sub

**Bill Rudd** · 02-01-2008, 02:55 AM

Thank you for the quick help last night, I am testing this now.

Yours Truly,
Bill Rudd

**Bill Rudd** · 02-01-2008, 03:54 AM

I think I am making progress, and thank you for the great search box! The 'Dim LSearchRow' and Dim LCoptToRow' rows I have changed from the 'As Integer' to 'As String' (my own guess, and I haven't been able to test it yet.

My real question is in lines (14) and (15); the code that I copied from the web has me searching for a set variable. How do I get the program to search for my 'String'?
[code]

(1) Sub box1()

(2) On Error GoTo Err_Execute

(3) Sub findString()
(4) Dim sFind As String

(5) sFind = Application.InputBox("Enter the search string", "Search...")
(6) If sFind > "" Then

(7) Dim LSearchRow As String
(8) Dim LCopyToRow As String

(9) 'Start search in row 2
(10) LSearchRow = 2

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

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

(14) 'If value in column B = "CAT", copy entire row to Sheet2
(15) If Range("B" & CStr(LSearchRow)).Value = "CAT" Then

(16) 'Select row in Sheet1 to copy
(17) Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
(18) Selection.Copy[code]

Yours Sincerely,
Bill Rudd

**Bill Rudd** · 02-13-2008, 02:49 AM

Hi guys, thanks for the previous help, and help on other forums here.

I am starting from scratch, and have written some very simple working code.

The first step to get this project to work is, (probably simple), an initial macro.

Step 1: It should run automatically when the workbook is opened
Step 2: It should search a column on Sheet 2. Some values will be empty, others will have values.
Step 3: Any empty cells should be ignored.
Step 4: Any cells with values should be copied to Sheet 10. (In an increasing column)
Step 5: When Sheet 2 has been checked, the process should be repeated for sheet 3, sheet 4, and sheet 5.

I'm guessing this is very easy. Can you help?

Yours Earnestly,
Bill Rudd

**royUK** · 02-13-2008, 05:24 AM

You can adjust the sheet numbers & column numbers in this to suit your needs

Option Explicit

Private Sub Workbook_Open()
    Dim rCopy  As Range
    Dim i      As Integer
    i = 2  'sheet to start from
    Do While i < 6
        With Worksheets(i)
            'copy cells with values in Column A
            Set rCopy = .Columns(1).SpecialCells(xlCellTypeConstants)
            rCopy.Copy Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        End With
        i = i + 1
    Loop
End Sub