Transfer 5000 records to a new spreadsheets with 50 records in e [Closed]

Report
Posts
1
Registration date
Sunday March 1, 2015
Status
Member
Last seen
March 1, 2015
-
venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
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