Ndtyjky

I'm asking VBA to find a string, then an ending string, and copy all the rows inbetween to paste into another sheet. However when I run the code it can't find the string. I've tested the code in a separate file using the same two strings as a start and ending point and it works just fine.

After looking online I see that the cell format could be causing this, but I don't see how to change it or even if that is the cause in this case. Any help is appreciated

Dim findrow As Long, findrow2 As Long

On Error GoTo errhandler



findrow = Range("A:A").Find("0667 John Smith", Range("A1")).Row

findrow2 = Range("A:A").Find("TTl Hrs For Employee", Range("A" & findrow)).Row



Range("A" & findrow & ":A" & findrow2).Select

Selection.Copy

Sheets("Sheet2").Select

Range("C12").Select

ActiveSheet.Paste



errhandler:

MsgBox "No Cells containing specified text found"

If I type the exact same thing in a new document the code finds it no problem. But in the original I get an code 91 error at the first "findrow" line.

Assuming the values are present, this worked just fine for me:

findrow = Range("A:A").Find("0667 John Smith", Range("A1")).Row

findrow2 = Range("A:A").Find("TTl Hrs For Employee", Range("A" & findrow)).Row

Range("A" & findrow & ":A" & findrow2).Copy Worksheets("Sheet2").Range("C12")

as long as the sheet to search was active. You may want to specify it in the code:

findrow = Worksheets("Sheet1").Range("A:A").Find("0667 John Smith", Worksheets("Sheet1").Range("A1")).Row

findrow2 = Worksheets("Sheet1").Range("A:A").Find("TTl Hrs For Employee", Worksheets("Sheet1").Range("A" & findrow)).Row

Worksheets("Sheet1").Range("A" & findrow & ":A" & findrow2).Copy Worksheets("Sheet2").Range("C12")

Update:
Found out what was wrong with it. For some reason the Find function can't find either string in the original sheet. If I copy the whole sheet into a new sheet and then have my code search for the strings it finds them and copies them just like it should.
I don't know what's causing it to do that and its not efficient but hey, it works. Thanks you guys for helping me trouble shoot, special thanks to @FunThomas

use fully qualified (up to worksheet object at least) range references and always specify Find() method LookAt and LookIn parameters not to implicitly assume last method usage (even from Excel UI) ones:

Dim firstCell As Range, lastCell As Range



With Worksheets("MySheetName") ' reference sheet where to serach for text (change "MySheetName" to your actual sheet name)

    Set firstCell = .Range("A:A").Find("0667 John Smith", Range("A1")) ' try and find first cell

    If Not firstCell Is Nothing Then ' if first cell found

        Set lastCell = .Range("A:A").Find(what:="TTl Hrs For Employee", lookat:=xlWhole, LookIn:=xlValues, after:=firstCell) ' try find last cell

        If Not lastCell Is Nothing Then ' if last cell found

            .Range(firstCell, lastCell).Copy Sheets("Sheet2").Range("C12")

        Else

            MsgBox "No Cells containing specified 'TTl Hrs For Employee'"

        End If

    Else

        MsgBox "No Cells containing specified '0667 John Smith'"

    End If

End With