Split excel file in different pages

[itsmezakky]

Hi,


I have a file which is 75000 records and 25 columns and the file size is very huge. I want to create a macro that would split the data in several files so that my file size should also be reduced.


Can any one help me, because this is very urgent and i am very new to vba.


Thanks in advance

 

[SirJB7]

Hi, itsmezakky!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


This is a repeatedly asked question, just try the above mentioned search.


Regards!

 

[itsmezakky]

Hi, I tried surfing and got some examples and wrote some part of code. However i am facing difficulty in designing the for loop. I basically need for every 2000 records the macro to create new file and paste the 2000 records and same till the end of the data.


I tried passing integer variable to range but it is not taking it. Can any one help to solve this. This is very urgent. I have my mentioned below


Option Explicit()


Sub Split()

Dim mybook As Workbook

Dim myRange As Range

Dim newBook As Workbook

Dim FileNm As String

Dim b As Integer

Dim nxt As Integer

b = 2

nxt = 2001

Set myBook = ThisWorkbook

FileNm = ThisWorkbook.Path & "" & myBook.Name & "1.xls"

Set newBook = Workbooks.Add

LastCell = ActiveSheet.UsedRange.Rows.Count

For i = 0 To LastCell

With newBook

myBook.Sheets("Sheet1").Rows("1:1").Copy .Sheets("Sheet1").Rows("1") // To copy the first row to each file

.SaveAs Filename:=FileNm, FileFormat:=xlNormal, CreateBackup:=False

myBook.Sheets("Sheet1").Rows("Val(b):Val(nxt)").Copy.Sheets(Sheet1").Rows("2)

.Close Savechanges:=False

End With

Next

End Sub

 

[SirJB7]

Hi, itsmezzaky!


Trying to follow as much as possible your posted code, please try with this update:

-----

[pre]

Option Explicit

Sub Split()
Const num = 100
Dim newBook As Workbook
Dim FileNm As String
Dim I As Long
With ThisWorkbook.Sheets("Hoja1")
For I = 2 To .UsedRange.Rows.Count Step num
FileNm = ThisWorkbook.FullName & "_" & Int((I - 1) / num) + 1 & ".xls"
Set newBook = Workbooks.Add
.Rows("1:1").Copy newBook.Sheets("Hoja1").Rows(1) '// To copy the first row to each file
.Range(.Rows(I), .Rows(I + num - 1)).Copy newBook.Sheets("Hoja1").Rows(2)
newBook.SaveAs Filename:=FileNm, FileFormat:=xlNormal, CreateBackup:=False
newBook.Close
Next
End With
End Sub

[/pre]
-----


Regards!