What I need is to open a text file, search through column A for a specific
number lets say "9135". this number will appear twice in column A so I want
to copy everything between the instance of 9135 and paste in a worksheet in
the next available empty row. I am not a programmer and I am completely lost
any help would be appreciated!

Armadeus

Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(9135)
if not rng is nothing then
set rng1 = columns(1).find(9135,rng)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
What I need is to open a text file, search through column A for a specific
number lets say "9135". this number will appear twice in column A so I
want
to copy everything between the instance of 9135 and paste in a worksheet
in
the next available empty row. I am not a programmer and I am completely
lost
any help would be appreciated!

Armadeus

Keeps giving the error 'First Instance not found' on a popup and doesn't
paste into worksheet.
"Tom Ogilvy" wrote in message
...
Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(9135)
if not rng is nothing then
set rng1 = columns(1).find(9135,rng)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
What I need is to open a text file, search through column A for a
specific
number lets say "9135". this number will appear twice in column A so I
want
to copy everything between the instance of 9135 and paste in a worksheet
in
the next available empty row. I am not a programmer and I am completely
lost
any help would be appreciated!

Armadeus

It is looking for the number 9135 in a cell by itself. If it is a string,
then it won't find it or if it is part of a string. Here is the adjustment
if that is the case:

Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(What:="9135", _
Lookin:=xlValues, LookAt:=xlPart)
if not rng is nothing then
set rng1 = columns(1).find(What:="9135",After:=rng, _
Lookin:=xlValues, LookAt:=xlPart)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

--
Regards,
Tom Ogilvy


"armadeus" wrote in message
...
Keeps giving the error 'First Instance not found' on a popup and doesn't
paste into worksheet.
"Tom Ogilvy" wrote in message
...
Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(9135)
if not rng is nothing then
set rng1 = columns(1).find(9135,rng)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
What I need is to open a text file, search through column A for a
specific
number lets say "9135". this number will appear twice in column A so I
want
to copy everything between the instance of 9135 and paste in a
worksheet
in
the next available empty row. I am not a programmer and I am
completely
lost
any help would be appreciated!

Armadeus

Thanks Tom that did it!
"Tom Ogilvy" wrote in message
...
It is looking for the number 9135 in a cell by itself. If it is a string,
then it won't find it or if it is part of a string. Here is the
adjustment
if that is the case:

Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(What:="9135", _
Lookin:=xlValues, LookAt:=xlPart)
if not rng is nothing then
set rng1 = columns(1).find(What:="9135",After:=rng, _
Lookin:=xlValues, LookAt:=xlPart)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

--
Regards,
Tom Ogilvy


"armadeus" wrote in message
...
Keeps giving the error 'First Instance not found' on a popup and doesn't
paste into worksheet.
"Tom Ogilvy" wrote in message
...
Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(9135)
if not rng is nothing then
set rng1 = columns(1).find(9135,rng)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
What I need is to open a text file, search through column A for a
specific
number lets say "9135". this number will appear twice in column A so
I
want
to copy everything between the instance of 9135 and paste in a
worksheet
in
the next available empty row. I am not a programmer and I am
completely
lost
any help would be appreciated!

Armadeus