Had a chance to update the file so you can select the CSV file. Copy the
code below into a standard module of workbook (not one of the 4 you will be
writing to). Change the file names to the files names of your files (you
will need to include a path if they are not in the same location as the files
the code is in). Change the AL, AZ, FL, NM to the appropriate matches that
you are looking for in column E. Then run the macro. It will ask you for a
file. Select the CSV file you want to distribute amongst the four files. If
there is no match for the column E value then a inputbox will appear asking
for an update.

Test it out and let me know how it works.

Mike


Option Explicit
Sub InputNDistrib()
Dim TZ1 As Workbook, TZ2 As Workbook, TZ3 As Workbook, TZ4 As Workbook
Dim cell As Range
Dim Filename As String, ResultStr As String, TimeZone As String, NewZone As
String
Dim FileNum As Long, Counter As Long
Dim sc As Integer, ec As Integer
Filename = Application.GetOpenFilename
Application.ScreenUpdating = False
If Filename = "False" Then End
Set TZ1 = Workbooks.Open("Book1.xls")
Set TZ2 = Workbooks.Open("Book2.xls")
Set TZ3 = Workbooks.Open("Book3.xls")
Set TZ4 = Workbooks.Open("Book4.xls")
Counter = 1
FileNum = FreeFile()
Open Filename For Input As #FileNum
Do While Seek(FileNum) <= LOF(FileNum)
If EOF(FileNum) Then Exit Do
Application.StatusBar = "Reading Line " & Counter & " of text file " &
Filename
Counter = Counter + 1
Line Input #FileNum, ResultStr
checkagain:
sc = InStr(InStr(InStr(InStr(1, ResultStr, ",") + 1, ResultStr, ",") + 1,
ResultStr, ",") + 1, ResultStr, ",") + 1
ec = InStr(sc + 1, ResultStr, ",")
TimeZone = Mid(ResultStr, sc, ec - sc)
Select Case TimeZone
Case "AL"
TZ1.Worksheets("Sheet1").Cells(TZ1.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1) = ResultStr
TZ1.Worksheets("Sheet1").Cells(TZ1.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1).TextToColumns _
DataType:=xlDelimited, Comma:=True
Case "AZ"
TZ2.Worksheets("Sheet1").Cells(TZ2.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1) = ResultStr
TZ2.Worksheets("Sheet1").Cells(TZ2.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1).TextToColumns _
DataType:=xlDelimited, Comma:=True
Case "FL"
TZ3.Worksheets("Sheet1").Cells(TZ3.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1) = ResultStr
TZ3.Worksheets("Sheet1").Cells(TZ3.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1).TextToColumns _
DataType:=xlDelimited, Comma:=True
Case "NM"
TZ4.Worksheets("Sheet1").Cells(TZ4.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1) = ResultStr
TZ4.Worksheets("Sheet1").Cells(TZ4.Sheets("Sheet1" ).Cells(65536,
5).End(xlUp).Row + 1, 1).TextToColumns _
DataType:=xlDelimited, Comma:=True
Case Else
NewZone = InputBox("The string " & Chr(10) & Chr(10) & """" & ResultStr
& """" & Chr(10) & Chr(10) & _
"does not contain a known timezone." & Chr(10) & Chr(10) & "Please
enter the desired timezone.", "Timezone Error")
ResultStr = Left(ResultStr, sc - 1) & NewZone & Mid(ResultStr, ec)
GoTo checkagain
End Select
Loop
Close
TZ1.Save
TZ2.Save
TZ3.Save
TZ4.Save
TZ1.Close
TZ2.Close
TZ3.Close
TZ4.Close
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub




"joetcb" wrote:


I receive a list daily of all new business', that are in a CSV format.
I am trying to figure out the formula that "if AZ is in column E then
sent the record Book 1, if AL is in column E then send the record to
Book 2, etc"
Basically I wish to place them in 4 separate books/sheets (for the 4
time zones)
Thanks in advance to all advice


--
joetcb
------------------------------------------------------------------------
joetcb's Profile: http://www.excelforum.com/member.php...o&userid=34534
View this thread: http://www.excelforum.com/showthread...hreadid=563326