I need a macro please

ExcelHelp - Jan 2, 2015 at 08:33 PM
 RayH - Jan 5, 2015 at 03:14 PM

I have 70,000 lines of data. I need to make 1400 files of 50 lines each from the original 70,000. So I'm hoping there is a macro that can pull the information from column A and column H in groups of 50 to make the new workbooks. I also need the data to begin on row two instead of row one.

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 3, 2015 at 04:17 AM
the result of the macro will open nearly three hundred files (workbooks). I hope you can handle them. try the following macro

as an experiment take only an extract of the main file upeto 151 rows and test the macro . I have not tested it.

try this macro

Sub test()
Dim j As Long, k As Long, r As Range, m As Long
Dim ppath As String
Application.ScreenUpdating = False
ppath = ThisWorkbook.Path
j = 1
With ThisWorkbook
With .Worksheets("sheet1")
k = j * 50 + 1
MsgBox k
m = k - 49
MsgBox m
Range(.Cells(m, 1), .Cells(k, 2).End(xlToRight)).Copy


With ActiveWorkbook.Worksheets("sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
ActiveWorkbook.SaveAs ppath & "\" & "ffile" & j & ".xlsx"

End With

j = j + 1
If j > 50 Then Exit Do
End With
End With
Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

there will be problem if you try once and some error crops up you have to close and DELETE all the files except the file in which macro is embedded (i.e. thisworkbook) have to be deleted because if the file already exists you cannot save two files in the same name. This is the problem with too many workbooks opened. because of 300 workbooks to be added and copied it will take a long time.
Thanks for your help with this. I'm wary to try, but will let you know if I do.
I tried your macro and it worked..
I removed the msgbox lines as that got annoying (ok for testing though).
So that there are not 300 files open (or however many you are creating) you simply need to close them after they are created.

With ActiveWorkbook.Worksheets("sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
ActiveWorkbook.SaveAs ppath & "\" & "ffile" & j & ".xlsx"
End With

Creating 50 files took less than 25 secs on my box.

The j variable would need to be increased from 50 to the number of files that get created. Or code could be added to count the number of rows in the sheets and compared with the k variable and exit once it is exceeded. Or k could be compared with an inputbox value entered by the user.
Lots of possibilities but overall a good solution.