formula
 

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 the
sent the record Book 1, if AL is in column E then send the record t
Book 2, etc"
Basically I wish to place them in 4 separate books/sheets (for the
time zones)
Thanks in advance to all advic

--
joetc
-----------------------------------------------------------------------
joetcb's Profile: http://www.excelforum.com/member.php...fo&userid=3453
View this thread: http://www.excelforum.com/showthread.php?threadid=56332

formula
 

To clarify, you have several rows of data and you want each row sorted to
different Workbooks based on the contents of column E. Copy this code into
the file and run. I think you may want to use another macro to import the
CSV file and then run this code. Let me know if you don't know how to do
this.

Mike

Option Explicit
Sub SortToBooks()
Dim TZ1 As Workbook, TZ2 As Workbook, TZ3 As Workbook, TZ4 As Workbook
Dim cell As Range
Application.ScreenUpdating = False
Set TZ1 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To
Different Books\Book1.xls")
Set TZ2 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To
Different Books\Book2.xls")
Set TZ3 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To
Different Books\Book3.xls")
Set TZ4 = Workbooks.Open("P:\Excel Help\Data Manipulation\Sort Rows To
Different Books\Book4.xls")
For Each cell In UsedRange.EntireRow
Select Case Cells(cell.Row, 5).Value
Case "AL"
cell.Copy
Destination:=TZ1.Worksheets("Sheet1").Cells(TZ1.Sh eets("Sheet1").Cells(65536,
5).End(xlUp).Row + 1, 1)
Case "AZ"
cell.Copy
Destination:=TZ2.Worksheets("Sheet1").Cells(TZ2.Sh eets("Sheet1").Cells(65536,
5).End(xlUp).Row + 1, 1)
Case "FL"
cell.Copy
Destination:=TZ3.Worksheets("Sheet1").Cells(TZ3.Sh eets("Sheet1").Cells(65536,
5).End(xlUp).Row + 1, 1)
Case "NM"
cell.Copy
Destination:=TZ4.Worksheets("Sheet1").Cells(TZ4.Sh eets("Sheet1").Cells(65536,
5).End(xlUp).Row + 1, 1)
Case Else
End Select
Next cell
TZ1.Save
TZ2.Save
TZ3.Save
TZ4.Save
TZ1.Close
TZ2.Close
TZ3.Close
TZ4.Close
Application.ScreenUpdating = True
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

formula
 

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