My macro pulls info from another file (2.txt)

When the file is generated, i have no control of the placement o
certain cells. But what i do have is a constant that the word BANK i
always in column E (regardles off the row) and cell i need is directl
after the word bank in column F. I need to copy the cell that's in
(thats directly to the right after the word Bank) and paste it into m
sheet101 worksheet in cell AL2.

Can anyone help

--
Message posted from http://www.ExcelForum.com

H

The following should get you started. It does assume that sheet101 is in the same workbook as the source data and this will have to be changed

Ton

Sub aaa(

Set finder = Range("e:e").Find(what:="BANK"

If Not finder Is Nothing The
Range("e:e").Find(what:="BANK").Activat
Sheets("sheet101").Range("a2").Value = ActiveCell.Offset(0, 1).Valu
End I

End Su

----- alexm999 wrote: ----

My macro pulls info from another file (2.txt

When the file is generated, i have no control of the placement o
certain cells. But what i do have is a constant that the word BANK i
always in column E (regardles off the row) and cell i need is directl
after the word bank in column F. I need to copy the cell that's in
(thats directly to the right after the word Bank) and paste it into m
sheet101 worksheet in cell AL2

Can anyone help


--
Message posted from http://www.ExcelForum.com

Alex

One way:

Sub CopyCell()

Dim RequiredBankCell As Range
Set RequiredBankCell = _
Columns("E:E").Find(What:="BANK", _
After:=Range("E1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
With Sheets("Sheet101")
RequiredBankCell.Offset(0, 1).Copy .Range("AL2")
End With

End Sub


Regards

Trevor


"alexm999 " wrote in message
...
My macro pulls info from another file (2.txt)

When the file is generated, i have no control of the placement of
certain cells. But what i do have is a constant that the word BANK is
always in column E (regardles off the row) and cell i need is directly
after the word bank in column F. I need to copy the cell that's in F
(thats directly to the right after the word Bank) and paste it into my
sheet101 worksheet in cell AL2.

Can anyone help?


---
Message posted from http://www.ExcelForum.com/

OK, im almost there...
My worksheets will change on a monthly basis...

Instead of sheet101 as the worksheet. How do I make it point t
"activeworksheet" so it doesnt matter what i call the worksheet nex
month...

--
Message posted from http://www.ExcelForum.com

Im trying to get the code you wrote to copy the cell directly to the
right of the word BANK and paste it to AL3 in the DAILY
OPERATIONS_2004.xls file, but it's not working... Any help? Getting
errors


---
Message posted from http://www.ExcelForum.com/

Here's the text file to give you a better idea of the dilemma...

The file is already opened and cells are being copied and pasted to th
other workbook. It's just the BANK problem thats all the way at th
bottom the spreadsheet.
Again, the row for the Cash to Bank and Cash from Bank information ca
change on a daily basis...
I just need the numbers from next to the work BAN

--
Message posted from http://www.ExcelForum.com

The code ran fine for me. No errors.

Go back to the original code.

this modification copies the cell to workbook Daily Operations_2004.xls
(assuming you spelled it correctly) to sheet101 in that workbook to cell
AL2.

Sub CopyCell()
Dim wkbk as Workbook

Dim RequiredBankCell As Range

set wkbk = Workbooks("DAILY OPERATIONS_2004.xls")
Set RequiredBankCell = _
Columns("E:E").Find(What:="BANK", _
After:=Range("E1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
With wkbk.Sheets("Sheet101")
RequiredBankCell.Offset(0, 1).Copy .Range("AL2")
End With

End Sub

--
Regards,
Tom Ogilvy



"alexm999 " wrote in message
...
Im trying to get the code you wrote to copy the cell directly to the
right of the word BANK and paste it to AL3 in the DAILY
OPERATIONS_2004.xls file, but it's not working... Any help? Getting
errors


---
Message posted from http://www.ExcelForum.com/

There are 2 files I'm working with...
3.TXT and DAILY OPERATIONS_2004.xls

I need the data directly in the cell to the right of the word BANK fro
3.txt and have it pasted to the Daily Operations_2004.xls file cel
AL1

--
Message posted from http://www.ExcelForum.com

Apparently you don't understand the code.

The activesheet is used to look for the word BANK, so if you don't want the
value in column F next to BANK pasted in cell AL2 of that same sheet, you
will have to specify where you do want it pasted - activesheet won't cut it
here.

--
Regards,
Tom Ogilvy

"alexm999 " wrote in message
...
OK, im almost there...
My worksheets will change on a monthly basis...

Instead of sheet101 as the worksheet. How do I make it point to
"activeworksheet" so it doesnt matter what i call the worksheet next
month...?


---
Message posted from http://www.ExcelForum.com/