So I have ~39,000 lines of data and I need to split it into populations of 300 over 120 sheets. Everything is in 1 column and it doesnt need sorted in any particular fashion.

THis is the code I was working with


Option Explicit
Sub ColumnToSheets()
Dim LR As Long, Rw As Long, Sz As Long
Sz = Application.InputBox(300, Type:=1)
If Sz = 0 Then Exit Sub
Application.ScreenUpdating = False
With ActiveSheet
LR = .Range(“A1” & .Rows.Count).End(xlUp).Row
For Rw = 1 To LR Step Sz
Sheets.Add after:=Sheets(Sheets.Count)
.Range(“A” & Rw).Resize(Sz).Copy Range(A1, [A40000])
Next Rw
.Activate
End With
Application.ScreenUpdating = True
MsgBox “Done”
End Sub


But its not really working, this part is erroring out
LR = .Range(“A1” & .Rows.Count).End(xlUp).Row
For Rw = 1 To LR Step Sz
I have really only ever coded alerts and prompts so I am kind of at a loss here

I can help you with the 5 components of physical fitness....but I'm not sure on this one.

Finally, proof that LJ does not know everthing.


You can rest easy now boys..

LJ;1570919 wrote:So I have ~39,000 lines of data and I need to split it into populations of 300 over 120 sheets. Everything is in 1 column and it doesnt need sorted in any particular fashion.

THis is the code I was working with


Option Explicit
Sub ColumnToSheets()
Dim LR As Long, Rw As Long, Sz As Long
Sz = Application.InputBox(300, Type:=1)
If Sz = 0 Then Exit Sub
Application.ScreenUpdating = False
With ActiveSheet
LR = .Range(“A1” & .Rows.Count).End(xlUp).Row
For Rw = 1 To LR Step Sz
Sheets.Add after:=Sheets(Sheets.Count)
.Range(“A” & Rw).Resize(Sz).Copy Range(A1, [A40000])
Next Rw
.Activate
End With
Application.ScreenUpdating = True
MsgBox “Done”
End Sub


But its not really working, this part is erroring out
LR = .Range(“A1” & .Rows.Count).End(xlUp).Row
For Rw = 1 To LR Step Sz
I have really only ever coded alerts and prompts so I am kind of at a loss here

I got this:

L/R = .Runge ....... (“A1” & .Rope.****).Ed....... (xlUp). mofo
For Raw = 1 two L/R Sheep Sz

Hope this hleps

Just record a macro inserting a new worksheet, then selecting/cutting/pasting to that new worksheet. Google how to do a "For Next i" loop and edit the macro for i = 1 to 300. I think your cell references will be something like 1300*i

gut;1571282 wrote:Just record a macro inserting a new worksheet, then selecting/cutting/pasting to that new worksheet. Google how to do a "For Next i" loop and edit the macro for i = 1 to 300. I think your cell references will be something like 1300*i

That just loops a finite number. I got the code figured out(a lot of help from a lot of people). I can post it if anyone wants.

LJ;1571343 wrote:That just loops a finite number. I got the code figured out(a lot of help from a lot of people). I can post it if anyone wants.

Nope, DGAF