select a range, copy it to a new sheet

[Guest]

For the VBA gurus.

I have a data table, 500,000 rows, in XL 2007.

A colleague wants this data put into XL 2000.

My take: write VBA code which splits the 500,000 rows into 10 tabs of 50,000
rows each. Then save the document in XL 2000 format and send to my colleague

My problem: I don't know how to do that.

Dave

 

[Ron de Bruin]

Hi Dave


Try this one on a copy of your workbook

Sub test()
Dim I As Long
Dim ws As Worksheet
Dim main As Worksheet

Set main = ActiveSheet
For I = 1 To 500000 Step 50000
Set ws = Worksheets.Add
main.Rows(I).Resize(50000).EntireRow.Copy ws.Range("A1")
Next I
End Sub