You need to you a VBA macro for that.
See macro [CopyData2OtherWorksheets] below...
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

'/===================================/
' Sub Purpose:
' start in cell A1 of the worksheet
' that you copy data from
'
'/===================================/
'
Public Sub CopyData2OtherWorksheets()
Dim dRowCount As Double, dCopyRows As Double
Dim dTotalRows2Copy As Double
Dim iWkstCount As Integer, i As Integer
Dim strWkstName As String

On Error GoTo err_Sub

'/- - - - V A R I A B L E S - - - - - /
dRowCount = -4999
dTotalRows2Copy = 300000 '# of rows to copy in total
dCopyRows = 5000 '# of rows to copy each time
'calc # of worksheets needed
iWkstCount = Int(dTotalRows2Copy / dCopyRows)
strWkstName = ActiveSheet.Name 'save name of starting wksht
'/- - - - - - - - - - - - - - - - - - /

Range("A1").Select

For i = 1 To iWkstCount
'start at original wksht that has the data
Sheets(strWkstName).Select
Sheets.Add
'rename the new worksheet
ActiveSheet.Name = "Wksht_" & i
'go back to the original worksheet
Sheets(strWkstName).Select
dRowCount = dRowCount + dCopyRows
'grab the data from the original worksheet
Rows(dRowCount & ":" & dRowCount + dCopyRows).Copy
'go to the new worksheet
Sheets("Wksht_" & i).Select
'copy the data to the new worksheet
ActiveSheet.Paste
Next i

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: CopyData2OtherWorksheets - Module: " & _
"Module1 - " & Now()
GoTo exit_Sub

End Sub
'/===================================/