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

[Tractor]

It's been many years and I'm just getting back into it for some personal stuff.

[Gergwheel1647545492]

This should work for you. Let me know if you have any more issues with this. I am only copying 15 colums, change that if you need to.

 

 

 

Option Explicit

 

Sub ColumnToSheets()

Dim LR As Long, Rw As Long, Sz As Long, valRng As Range

Sz = Application.InputBox(300, Type:=1)

If Sz = 0 Then Exit Sub

Application.ScreenUpdating = False

With ActiveSheet

LR = Cells(Rows.Count, 1).End(xlUp).Row

For Rw = 1 To LR Step Sz

Range(Cells(Rw, 1), Cells(Rw + (Sz - 1), 15)).Copy

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "rows " & Rw & " - " & Rw + Sz - 1

ActiveSheet.Paste

Sheets(1).Select

Next Rw

.Activate

End With

Application.ScreenUpdating = True

MsgBox ("Done")

End Sub

[Tractor]

While we're on the topic, anyone want to point me in a direction? I'm the ultimate "has been" I has been a guru back in early VBDOS, and the first couple windows versions of VB back in the mid `90's. I downloaded VB Studio and managed to make a simple profit calculator pretty quickly.

 

I'm wanting to expand into creating a type of accounting software that will store my data in a database as part of my ongoing learning and hopefully in a few years expand into automated trading and accounting with it. I was happy to see VB has a wizard that would make the job very easy, but it seems I must have an ACCESS database file or similar already created in order to use the wizard. I can't find a way around this.

 

Any ideas? I know I should probably start by learning to write/read data from a file and will probably end up starting there and working through everything anyway.

[Gergwheel1647545492]

store my data in a database

 

I must have an ACCESS database file or similar already created in order to use the wizard. I can't find a way around this.

 

I don't quite understand what you are asking. You want to eventually store data in a database, but you don't want to use Access to store it? so you want to use Oracle, SQL Server or MySQL?

[Tractor]

Actually any database format will work including access. Its sort of a chicken/egg problem. The wizard won't work unless you already have a database file and I won't have a database file until I finish the program and start inputing data.

 

I was thinking I could get access and make the database file then my problems should be solved, but thats sort of annoying.

 

I would like to use the wizard, by default it will make just about 90% of the program for me and I'd just need to customize it and add in some calculations.