Import csv files, delete rows, arrange columns ....

[xadnora]

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

[Jim Thomlinson]

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
>

[xadnora]

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
> >

[xadnora]

Also I forgot to ask....do you think this would be easier to accomplish in
Access? Even though I do not know Access very well.

"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
> >

[Tom Ogilvy]

Since the csv's are in a standard order, you can just put the required order
above the existing column headings, then sort left to right. That assumes
your headers are not really numbers. If they are numbers and you just want
to sort in numerical order, you don't need to add a dummy header.

The code would be

Range("A1").CurrentRegion.Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlno,OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight

rwMin = Application.Match(application.Min(columns(3)), _
Columns(3),0)
rwMax = Appliction.Match(application.Max(columns(3)), _
Columns(3),0)

rows(rwMin).copy Destination:=worksheets("Main").Cells( _
rows.count,1).End(xlup)(2)
rows(rwMax).copy Destination:=worksheets("Main").Cells( _
rows.count,1).End(xlup)(2)


------
if you need to put in a row, then
Rows(1).Insert
Range("A1:AF1").Value = Array(16,3,9, . . .,2)

do the sort and copy

Rows(1).Delete



--
Regards,
Tom Ogilvy


"xadnora" <[email protected]> wrote in message
news:[email protected]...
> 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").Ent
ireColumn.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
>

[Jim Thomlinson]

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
> > >

[Jim Thomlinson]

If you have access then this is fairly easy. Open a new blank databse. You
will be asked to save the database. Click on file and then on Get External
Data -> import. Switch to File Type Text. Find your file and click Open. A
wizard will walk you through extracting the data. You can now create a query
off of the table to extract only the columns you want in the order you want.
The nice thing about doing it this way is that you are not throwing away any
source data. It is a perfectly acceptable option.

Of course Tom has some code for you that might just get you around the
problem that you are having. Take a look at what he has. It will do the
sorting for you...

HTH

"xadnora" wrote:

> Also I forgot to ask....do you think this would be easier to accomplish in
> Access? Even though I do not know Access very well.
>
> "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
> > >

[xadnora]

Thank you Tom and Jim for all your wonderful help. You guys are great. With
the code you provided and some recorded macros, I think I will be able to
accomplish this task....hopefully. Thanks again.

I'm sure it won't be the last time you pull me out of a jam

xadnora

"Tom Ogilvy" wrote:

> Since the csv's are in a standard order, you can just put the required order
> above the existing column headings, then sort left to right. That assumes
> your headers are not really numbers. If they are numbers and you just want
> to sort in numerical order, you don't need to add a dummy header.
>
> The code would be
>
> Range("A1").CurrentRegion.Sort _
> Key1:=Range("A1"), Order1:=xlAscending, _
> Header:=xlno,OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlLeftToRight
>
> rwMin = Application.Match(application.Min(columns(3)), _
> Columns(3),0)
> rwMax = Appliction.Match(application.Max(columns(3)), _
> Columns(3),0)
>
> rows(rwMin).copy Destination:=worksheets("Main").Cells( _
> rows.count,1).End(xlup)(2)
> rows(rwMax).copy Destination:=worksheets("Main").Cells( _
> rows.count,1).End(xlup)(2)
>
>
> ------
> if you need to put in a row, then
> Rows(1).Insert
> Range("A1:AF1").Value = Array(16,3,9, . . .,2)
>
> do the sort and copy
>
> Rows(1).Delete
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "xadnora" <[email protected]> wrote in message
> news:[email protected]...
> > 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").Ent
> ireColumn.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
> >
>
>
>

[xadnora]

Sorry to bug you, but during the sort procedure, how do I sort the entire
column for the dummy header. It is sorting the dummy header row but the
column data is not sorted with it. Thanx.

xadnora

"Tom Ogilvy" wrote:

> Since the csv's are in a standard order, you can just put the required order
> above the existing column headings, then sort left to right. That assumes
> your headers are not really numbers. If they are numbers and you just want
> to sort in numerical order, you don't need to add a dummy header.
>
> The code would be
>
> Range("A1").CurrentRegion.Sort _
> Key1:=Range("A1"), Order1:=xlAscending, _
> Header:=xlno,OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlLeftToRight
>
> rwMin = Application.Match(application.Min(columns(3)), _
> Columns(3),0)
> rwMax = Appliction.Match(application.Max(columns(3)), _
> Columns(3),0)
>
> rows(rwMin).copy Destination:=worksheets("Main").Cells( _
> rows.count,1).End(xlup)(2)
> rows(rwMax).copy Destination:=worksheets("Main").Cells( _
> rows.count,1).End(xlup)(2)
>
>
> ------
> if you need to put in a row, then
> Rows(1).Insert
> Range("A1:AF1").Value = Array(16,3,9, . . .,2)
>
> do the sort and copy
>
> Rows(1).Delete
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "xadnora" <[email protected]> wrote in message
> news:[email protected]...
> > 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").Ent
> ireColumn.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
> >
>
>
>