65000 Row limit - splitter help!

[moglione1]

Hi all,

I am fairly new to VBA and have inherited the code below. I know the
code basically says if the data is over 65000 rows then put onto the
next sheet.

Now I kind of need to do this again but this time if Sheet 2 is over
65000 then continue copying the data onto sheet 4.

If it helps : The data is been copied into the file from a .txt file

Code:
'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1

'*******Add new sheet at 65000*******
If lRow = 65000 Then
Sheets("Sheet2").Select
lRow = 1
End If
'Write to the worksheet
For i = 0 To UBound(vaFields)
ActiveSheet.Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop
MsgBox "Complete "
Close lFNum
Application.ScreenUpdating = True
Exit Sub

 

[Guest]

put this above the original DO statement

Dim ShCou as integer
ShCou =1


and edit this part of the code to read

'*******Add new sheet at 65000*******
If lRow = 65000 Then
Sheets(ShCou).Select
ShCou = ShCou +1
lRow = 1
End If



note, this will automatically choose the next sheet in order of how they are
placed in the excel workbook, as the ShCou is using an Index value.

 

[Tom Ogilvy]

'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1

'*******Add new sheet at 65000*******
If lRow = 65000 Then
' changed line
ActiveSheet.Next.Select
lRow = 1
End If
'Write to the worksheet
For i = 0 To UBound(vaFields)
ActiveSheet.Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop
MsgBox "Complete "
Close lFNum
Application.ScreenUpdating = True
Exit Sub

 

[Guest]

I'm sorry that first part should read

Dim ShCou as integer
ShCou = 2

 

[Guest]

sometimes i feel stupid reading these posts, you'd think by now i'd know some
of these simple methods I don't pay attention too.

--
When you lose your mind, you free your life.

'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1

'*******Add new sheet at 65000*******
If lRow = 65000 Then
' changed line
ActiveSheet.Next.Select
lRow = 1
End If
'Write to the worksheet
For i = 0 To UBound(vaFields)
ActiveSheet.Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop
MsgBox "Complete "
Close lFNum
Application.ScreenUpdating = True
Exit Sub

 

[NickHK]

moglione1,
May be applicable, but I understand future versions of Excel will extend the
row limit (to ~1 million ??).
So it may be worth checking ActiveSheet.Rows first to see how many you have
to work with.

NickHK

 

[Guest]

How exactly does Excel know to use this code?...is there a UDF or do you have
to import the data from text, or from Access.....what's the trigger?

Also, I'm relatively new to VBA also, when i copied this code into my vba
module...there has to be a beginning Sub function...I don't see that in the
code.

Thanks