I need a macro please [Closed]

Report
-
 RayH -
Hello,

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 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
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")
Do
k = j * 50 + 1
MsgBox k
m = k - 49
MsgBox m
Range(.Cells(m, 1), .Cells(k, 2).End(xlToRight)).Copy

Workbooks.Add

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
Loop
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"
ActiveWorkbook.Close
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.