Here is an example of the split function in action. Plcae this code in a
sheet or module. Put some data in one cell like

This, that, The other

Make sure that the cursor is placed in the cell and then run this code

Private Sub SplitCSV()
Dim aryCSVValues As Variant
Dim rngPasteTo As Range
Dim intCounter As Integer
Dim wksPasteTo As Worksheet

aryCSVValues = Split(ActiveCell.Value, ",")
Set wksPasteTo = Worksheets.Add
Set rngPasteTo = wksPasteTo.Range("A1")

For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
aryCSVValues(intCounter) = Trim(aryCSVValues(intCounter))
rngPasteTo.NumberFormat = "@"
rngPasteTo.Value = aryCSVValues(intCounter)
Set rngPasteTo = rngPasteTo.Offset(1, 0)
Next intCounter
End Sub

HTH

"xadnora" wrote:

I am really new to VBA coding but I am trying to learn as I go. I am not
aware of the split function you are referencing. Where would be the best
place to learn about this procedure? You may be correct....that may be
easier. I was using this method because there was so much data manipulation
involved once I inputed the data, I thought it would be easy to click on each
tab and see the data without actually touching the original CSV files as they
take hours of simulation to produce. And no I cannot change the exporting
format of these files. Thank you for your input. If you have any
suggestions about the split function, I am all ears :)

"Jim Thomlinson" wrote:

Wow... I assume you can not change the format of the CSV when it is
created... Have you considered reading the CSV one line at a time and using
the split function to load an array. You can then just output the correct
elements of the array in the proper order. I wonder if that might not be
easier. Looking at your code you know what you are doing so I do not assume
to over ride your judgement. Just to offer an alternative.



"xadnora" wrote:

I am importing 34 csv files (each into a different sheet) into one workbook
with one "Main" sheet. e.g. "Main", "1", "2", ..... , "34". Once these are
imported, I have to delete unwanted data from certain columns. Then organize
the columns to a specific order to match the "Main" sheet. The column
heading is located in range("A16:AF16") on the csv imported tabs. Once it is
arranged in this format, I have to find the max and min of certain columns
and select that corresponding row of data to copy to the "main" sheet. I
just need help getting started with the sorting process. This is what I have
so far. TIA for any help you can provide.

Sub OpenCSVFiles()
Dim wks As Worksheet
Dim wkbk As Workbook
Dim rngchan As Range
Dim wksmain As Worksheet
Dim i As Integer

Set wksmain = Sheets("Main")

With Application.FileSearch
.NewSearch
.LookIn = "C:\test\csv"
.SearchSubFolders = False
.Filename = ".csv"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(Filename:=.FoundFiles(i))

For Each wks In wkbk.Worksheets
dblLastRow = Range("A65535").End(xlUp).Row
Set rngchan = Range("A16:AF16")
wks.Copy after:=ThisWorkbook.Sheets(i) 'Change sheet name

Range("a1,b1,c1,d1,e1,h1,j1,m1,n1,v1:x1,z1,aa1:ad1 ,ai1,al1:aq1,ax1:ba1").EntireColumn.Delete

This is where I need to arrange columns in a certain order from the column
heading in rngchan. In the "main" sheet it is in a certain order. e.g.main
sheet 1,2,3,...34 and on csv tabs it is in 16,3,9,...2

Then I have to find max and min of column 3 and then copy those rows of data
to the "main" sheet in a specified location.



Next wks
wkbk.Close
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub