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

[ben]

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
>
>

[ben]

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
> >
> >

[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

--
Regards,
Tom Ogilvy


"moglione1" <moglione1.22vjx1_1139341502.8451@excelforum-nospam.com> wrote
in message news:moglione1.22vjx1_1139341502.8451@excelforum-nospam.com...
>
> 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
>

[ben]

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" <moglione1.22vjx1_1139341502.8451@excelforum-nospam.com> wrote
> in message news:moglione1.22vjx1_1139341502.8451@excelforum-nospam.com...
> >
> > 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
> >
>
>
>

[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

"moglione1" <moglione1.22vjx1_1139341502.8451@excelforum-nospam.com> wrote
in message news:moglione1.22vjx1_1139341502.8451@excelforum-nospam.com...
>
> 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
>

[Jeff]

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
>
>