Autosort on Open

[ir121973]

Hi, I wonder if someone could help me please.

With some help along the way, I've put together the following script which splits a master file into smaller ones.

Sub SplitTextFile()
Dim sFile As String  'Name of the original file
Dim sText As String  'The file text
Dim lStep As Long    'Max number of lines in the new files
Dim vX, vY           'Variant arrays. vX = input, vY = output
Dim iFile As Integer 'File number from Windows
Dim lCount As Long   'Counter
Dim lIncr As Long    'Number for file name
Dim lMax As Long     'Upper limit for loop
Dim lNb As Long      'Counter
Dim lSoFar As Long   'How far did we get?
Dim myRng As Range


On Error GoTo ErrorHandle


sFile = Application.GetOpenFilename()
If sFile = "False" Then Exit Sub


lStep = Application.InputBox("Max number of lines/rows?", Type:=1) + 1




lStep = lStep - 1


sText = _
CreateObject("Scripting.FileSystemObject").OpenTextFile(sFile).ReadAll
vX = Split(sText, vbLf)
sText = ""


With Worksheets(1)
Set myRng = .Range("A1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With


With myRng
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, _
Header:=xlYes
End With


Do While lSoFar < UBound(vX)
   If UBound(vX) - lSoFar >= lStep Then
      ReDim vY(lStep)
      lMax = lStep + lSoFar
   Else
      ReDim vY(UBound(vX) - lSoFar)
      'Last row to copy is last row in vX
      lMax = UBound(vX)
   End If


   If lSoFar = 0 Then
   lNb = 0
   lMax = lMax
   Else
   lMax = lMax - 1
   lNb = 1
   End If


   For lCount = lSoFar To lMax
   vY(0) = vX(0)
      vY(lNb) = vX(lCount)
      lNb = lNb + 1
   Next
   lSoFar = lCount
   iFile = FreeFile
   lIncr = lIncr + 1
   Open sFile & "-" & lIncr & ".csv" For Output As #iFile
      Print #iFile, Join$(vY)
   Close #iFile


Loop
Erase vX
Erase vY
Exit Sub


ErrorHandle:
MsgBox Err.Description & " Procedure SplitTextFile"
End Sub

The script works, except for this section:

With Worksheets(1)
Set myRng = .Range("A1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With


With myRng
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, _
Header:=xlYes
End With

What I'm trying to do is sort the open 'master file' before the file is split but it's not sorting the range.

I just wondered whether someone could look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

 

[mole999]

is it really the first sheet, if you step through the code is it referencing where you expect. Does it jump to the error handler

 

[ir121973]

Hi @mole999, thank you for taking the time to reply.

Yes it is the first and only sheet in the workbook, which incidentally is in a .csv format.

When I step through the code, the "myRng" isn't being set, so it suggests that I'm not managing to grab the open workbook.

Regards

Chris