Transfer 5000 records to a new spreadsheets with 50 records in e

Closed
khanjandesai Posts 1 Registration date Sunday March 1, 2015 Status Member Last seen March 1, 2015 - Mar 1, 2015 at 03:05 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 4, 2015 at 12:32 AM
I have a excel file which contains 5000 recores, i want to transfer 5000 records to a new spreadsheets with 50 records in each new spreadsheets i.e total 100 sheets, so please help me
Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 4, 2015 at 12:32 AM
in the workbook where the original data is there
hit alt5 F11
click insert-module
in that window copy the macro given below
save the file as macro enabled excel file thkat is .xlsm)
open another excel file and save it as .xlsx file
keep both files open
see the code
set wb=..........
in the macro
change it to the saved xlsx file

save again the first file

run the macro.

at first take a file with minimum data of about 160 rows (instead of 5000 rows) and test the macro
then you can run macro for original file

the macro to be in the first file is


Sub test()
Dim j As Integer, k As Integer, wb As Workbook
j = 2
k = 50
Set wb = Workbooks("book2.xlsx")
Do
With ThisWorkbook
With .Worksheets("sheet1")
Range(.Cells(j, 1), .Cells(j + k - 1, 1)).EntireRow.Copy
With wb
With .Worksheets("sheet1")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
End With
End With
j = j + k
If .Cells(j, 1) = "" Then Exit Do

End With
End With
Loop
MsgBox "macro over":




End Sub
0