Help me tweak working VBA Code

[radneo]

I have several CSV files, each with a column of numbers (same size). The following block of code copies the columns from all the CSV files into a single workbook - where each column is copied to a new sheet in the workbook. Imagine a workbook with several sheets where each sheet is a column of numbers - this is the output.

The CSV files are named 01, 02,..., 032. Here is the problem: in the new workbook, the sheets are arranged as 01, 010, 011 and so on. The numerical order is ignored - I would like the sheets to be arranged as 01, 02, 03, and so on. Can someone please help me tweak this code to do so? Thank you very much!!

Sub test()     Dim myDir As String, fn As String, wb As Workbook 
    Set wb = ActiveWorkbook 
    With Application.FileDialog(msoFileDialogFolderPicker) 
        If .Show Then myDir = .SelectedItems(1) & "\" 
    End With 
    If myDir = "" Then Exit Sub 
    fn = Dir(myDir & "*.csv") 
    Do While fn <> "" 
        With Workbooks.Open(myDir & fn) 
            .Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count) 
            .Close False 
        End With 
        fn = Dir 
    Loop 
End Sub

[Bob Phillips]

Is this what you mean

Sub test()     
Dim myDir As String, fn As String, wb As Workbook 
    Set wb = ActiveWorkbook 
    With Application.FileDialog(msoFileDialogFolderPicker) 
        If .Show Then myDir = .SelectedItems(1) & "\" 
    End With 
    If myDir = "" Then Exit Sub 
    fn = Dir(myDir & "*.csv") 
    Do While fn <> "" 
        With Workbooks.Open(myDir & fn) 
            .Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count) 
	    wb.Sheets(wb.Sheets.Count).Name = Left$(fn, InstrRev(fn, ".")-1)
            .Close False 
        End With 
        fn = Dir 
    Loop 
End Sub

[radneo]

Hi xld, thank you for replying. Unfortunately, it does not work - please look at the attached screenshot. I would like the sheets to be in order - 01, 02, 03, 04, 05, 06,....,032.

Capture.jpg

[Bob Phillips]

You will probably have to sort then. See http://www.cpearson.com/excel/sortws.htm

[radneo]

thanks so much for the link! I will try it out.