Auomatically populating "Sheet2" when "Sheet1" is full

[longbow007]

Hello, I am using Excel 2003 on Windows XP. I need to import over 71,000 rows of data from a text file.

Currently, I open Excel 2003 and select: Data \ Import External Data \ Import Data

Then I select H:\My Documents\Data.txt file.

I then use the Text Import Wizard to finish importing my data.

I can also create a macro to do this by using the Macro Recorder.

Sub Import_Data()
'
' Import Data Macro
'
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;H:\My Documents\Data.txt", Destination:=Range("A1"))
        .Name = "Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 13
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "~"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

However, I have over 71,000 employees to be added to my workbook and my Excel 2003 only has 65536 rows in each worksheet.

Can someone be please kind enough to help me with some VBA code so that when I import my data using the Text Import Wizard (as shown in my code above), it will firstly populate "Sheet1" and when it is full (i.e. over 65536 rows), the remainder of the data will automatically populate into "Sheet2" as shown in my attached example workbook.

Any help would be greatly appreciated.

Kind regards,

Chris