Need some macro help please

i got this
Sub test()
Dim oThis As Worksheet
Dim oWB As Workbook
Dim iLastRow As Long
Dim iStart As Long
Dim i As Long

Set oThis = ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = "York Region CCAC Test" Then
If iStart > 0 Then
Set oWB = Workbooks.Add
oThis.Range("A" & iStart & ":A" & i - 1).Copy
oWB.Worksheets(1).Range ("A1")
oWB.SaveAs Filename:="H:\Finance Team\Daniel\EFT\test\" & oThis.Cells(iStart + 2, "A") & ".xls"
End If
iStart = i
End If
Next i

End Sub



but i keep getting the error:
Run-time error '438'
Object doesn't support this property or method

any ideas?

I'm not sure where the "York Region..." stuff is coming from, but going with your original example using "Repeating" and a line between the groups of data, this seems to work for me:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> test()
<SPAN style="color:#00007F">Dim</SPAN> myRng <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, firstaddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> newWB <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, newName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#007F00">'sets the file path to a variable to use later</SPAN>
<SPAN style="color:#00007F">Const</SPAN> myPath = "H:\Finance Team\Daniel\EFT\test\"

<SPAN style="color:#007F00">'sets the range of data to check</SPAN>
<SPAN style="color:#00007F">With</SPAN> ThisWorkbook.Sheets("Sheet1")
    <SPAN style="color:#00007F">Set</SPAN> myRng = .Range("A1", .Range("A65536").End(xlUp))
End <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#007F00">'search data</SPAN>
<SPAN style="color:#00007F">With</SPAN> myRng
    <SPAN style="color:#00007F">Set</SPAN> c = .Find("Repeating")
    
    <SPAN style="color:#007F00">'if keyword is found...</SPAN>
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        firstaddress = c.Address
        <SPAN style="color:#00007F">Do</SPAN>
            <SPAN style="color:#007F00">'create new workbook</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> newWB = Workbooks.Add
            
            <SPAN style="color:#007F00">'set the name for the new workbook (w/file extension)</SPAN>
            newName = c.Offset(2, 0).Value & ".xls"
                        
            <SPAN style="color:#007F00">'copy the current range of data</SPAN>
            Range(c, c.<SPAN style="color:#00007F">End</SPAN>(xlDown)).Copy
            
            <SPAN style="color:#00007F">With</SPAN> newWB
                
                <SPAN style="color:#007F00">'paste data to Sheet1 in new workbook</SPAN>
                .Sheets(1).Paste
                
                <SPAN style="color:#007F00">'save new workbook using path and name variables</SPAN>
                .SaveAs Filename:=myPath & newName
                
                <SPAN style="color:#007F00">'close new workbook</SPAN>
                .Close
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            
            <SPAN style="color:#007F00">'find next instance of keyword</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> firstaddress
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

End <SPAN style="color:#00007F">Sub</SPAN></FONT>

hi, thanks for the reply

but i get an error:
Run-time error '9'
subscript out of range

any idea of how to fix this?

You could start by stating what line is highlighted when you get the error

fixed that error, but i get a new one now:
SaveAs Method of workbook failed

.SaveAs Filename:=myPath & newName

nevermind i fixed that, but it only copies and paste the first 2 lines, can i expand the range?

If it is only copying the first 2 lines, I'm assuming you have blank lines in your actual data, yes? All I have to go off of is the sample you posted in the original post, which does not have a blank line.

This *could* be a little trickier--do you have the same number of lines for each group of data?

yeah, sorry i didn't think extra space lines would matter. my fault.

no, not each section has the same number of lines

here's a sample file
http://www.monkeydan.com/Export2.csv

this one has the same number of lines in it, but usually it won't

Ah, ok.

If you edit what I posted to look for "Vendor ID" instead of "Repeating," I think it should work since there are no blanks in the data between Vendor ID and Totals.

As the code is written right now, it will copy everything in column A from Vendor ID to Totals. But ONLY from column A.

What exactly are you wanting to copy?