Related:
- I need a macro please
- Spell number in excel without macro - Guide
- Macro excel download - Download - Spreadsheets
- Excel macro to create new sheet based on value in cells - Guide
- Run macro on opening workbook - Guide
- Excel vba assign macro to button programmatically - Guide
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
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
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.
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.
Jan 3, 2015 at 07:58 PM
Jan 5, 2015 at 03:14 PM
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.
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.