How do I Split a column into new workbooks that get saved and named?

[CarlosMD]

Hi I'd like to write a code that can split every 1000 rows of a column into a new workbook. I've got a VBA code that does that, however, I'd also like it to save the new files as a csv, add the same header of "Id" to each new workbook, and name the files as "1","2","3", etc. I'll post the VBA I have so far. I appreciate any help. Thank you!



Sub split()

'Set dimensions
Dim Table As Range, TableArray(), _
CutValue As Integer, Cntr As Integer, _
TempArray(), Width As Integer, _
x As Integer, y As Integer, _
Height As Long, Rep As Integer, _
LoopReps As Long

'Get data
Set Table = Application.InputBox("Specify range to copy", _
Default:=ActiveCell.CurrentRegion.Address, Type:=8)
CutValue = InputBox("How many rows should the chunks be?", _
Default:=1000)
Width = 1
Height = Table.Rows.Count

'Write to array
TableArray = Table
ReDim TempArray(1 To CutValue, 1 To Width)
Rep = Application.WorksheetFunction.RoundUp(Height / CutValue, 0)
LoopReps = CLng(CutValue)

'Loop through all new sheets
For Cntr = 0 To Rep - 1
If Height - Cntr * CLng(CutValue) < CLng(CutValue) Then _
LoopReps = Height - Cntr * CLng(CutValue)

For x = 1 To Width
For y = 1 To LoopReps
TempArray(y, x) = TableArray(y + Cntr * CLng(CutValue), x)
Next y
Next x

Workbooks.Add
Range("A1").Resize(LoopReps, Width) = TempArray
Next Cntr

End Sub

 

[Fluff]

Duplicate to: Need help writing a VBA Code to Split a column into new workbooks

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.