Anyone know VBA?

• LJ

  

  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
• Ironman92

  

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

  

  Finally, proof that LJ does not know everthing.
  
  
  You can rest easy now boys..
• Belly35

  

  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
• gut

  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
• LJ

  

  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.
• Commander of Awesome

  

  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