Sorting left to right all files in a directory

[Don4000]

I’m a complete newbie, and I have several problems that I hope you kind people could help with. I have been searching here for code that will help me and found some partial solutions. Here is problem #1
I have several files in C:\DATA about 30, but the number changes. All files have one sheet with data. The sheets have the same column headings, but not in the same order. So in File1 Name may be in column A, and in File2 it could be in column B. The files are sent to me so I’m not in control of the layout.
What I need is a macro that sorts the columns left to right for all files in the directory.
I know that Excel will do this, in Data, Sort, Options, Left to Right, but its time consuming to do this for 30 files.


Here is the code I found. Could someone modify it to repeat this for all files in a directory?


With ActiveSheet.UsedRange
For Each rw In .Rows
rw.Sort Key1:=Range("A" & rw.Row), Order1:=xlAscending, Orientation:=xlLeftToRight
Next rw
End With
End Sub


Your help would be appreciated
Thanks Don4000

 

[Don4000]

People

I have a solution to my own problem.
Sub ProcessAll opens the file in the directory, one at a time. Then Sub Fixit sorts left to right. Is it possible to do this without opening the file? Maybe someone can tell me
This may be of use to someone else


Sub ProcessAll()
'
' Opens all files in a directory,one at a time,then carries out what whatever you wish
'using the fixit code below


Dim Wb As Workbook, sFile As String, sPath As String
Dim itm As Variant
Dim strFileNames As String

sPath = "C:\DATA\" 'change this directory to what ever directory you want to work on'

' Retrieve the current xl files in directory
sFile = Dir("C:\DATA\" & "*.xls") 'change this directory to what ever directory you want to work on'
Do While sFile <> ""
strFileNames = strFileNames & "," & sFile
sFile = Dir()
Loop

' Open each file found
For Each itm In Split(strFileNames, ",")
If itm <> "" Then
Set Wb = Workbooks.Open(sPath & itm)


Call Fixit 'this runs my macro from above
Wb.Close True
End If
Next itm

End Sub
Sub Fixit()
'
' Fixit Sorts all columns left to right
' You can replace this code with whatever you want to perform'

'
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

 

[tusharm]

Thanks for sharing your solution.

The answer to your question, unfortunately, is no.

People

I have a solution to my own problem.
Sub ProcessAll opens the file in the directory, one at a time. Then Sub Fixit sorts left to right. Is it possible to do this without opening the file? Maybe someone can tell me
{snip}