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


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=509484

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.




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


"moglione1" wrote:


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


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=509484

I'm sorry that first part should read

Dim ShCou as integer
ShCou = 2

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


"ben" wrote:





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.




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


"moglione1" wrote:


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


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=509484

'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

--
Regards,
Tom Ogilvy


"moglione1" wrote
in message ...

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


--
moglione1
------------------------------------------------------------------------
moglione1's Profile:
http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=509484

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.


"Tom Ogilvy" wrote:

'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

--
Regards,
Tom Ogilvy


"moglione1" wrote
in message ...

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


--
moglione1
------------------------------------------------------------------------
moglione1's Profile:
http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=509484

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

"moglione1" wrote
in message ...

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


--
moglione1
------------------------------------------------------------------------
moglione1's Profile:
http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=509484

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

"moglione1" wrote:


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


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=509484