Distribute 35000 Rows equally into 6 different excel sheets

35,000 records / 6 sheets = 5,833.33 records per sheet so things will not come out EXACTLY equal but it is a simple matter to break up one sheet of 35,000 into 6 sheets of 5,833 using cut and paste or Copy Sheet.

Hi,

Try,


HTH

a = Sheets("Sheet1").[a1].CurrentRegion.Value

Click to expand...

HI Krishnakumar, just wondering if you could explain me what does [a1] mean...I was trying to figure it out, but I can´t
Oh... in that expression I quoted, you are setting "a" as a range?
Thanks!!!!!!

Hi XLgods,

I needed to prevent the copy paste activity on a particular excel sheet....Please can any one help me...i need it urgent

Hi,

Sub kTest()
Dim a, w(), i As Long, n As Long, h As Long, c As Byte
a = Sheets("Sheet1").[a1].CurrentRegion.Value
h = Int((UBound(a, 1) + 1) / 6) + 1
ReDim w(1 To h, 1 To UBound(a, 2))
n = 1
For i = 1 To UBound(a, 1)
For c = 1 To UBound(a, 2): w(n, c) = a(i, c): Next
If n = h Then
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.[a1].Resize(h, UBound(a, 2)) = w: n = 0
End If: n = n + 1
Next
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.[a1].Resize(n - 1, UBound(a, 2)) = w
End Sub

The Above code some times works fine but some times dose not and give the following error

Run-time error 1004
Application-defined or object-define error

when I click on debug it highlights the following

ActiveSheet.[a1].Resize(h, UBound(a, 2)) = w


Kindly help me out what is the problem.

Regards,
Shafiq