Looking to split a large excel file by rows and keep the header

[flyguy]

I am trying to split up an excel file that has multiple lines, I want to break this into groups of 500 lines and retain the original header info for each file. This file will always have the same header just different amount of lines each time. Is this possible to do with a macro/vba. I have no real experience with macros for programming. It would be great if it could auto save as file1-file2 etc. file name does not matter. Just breaking apart manually is time consuming and looking to see if it can be automated.
The file starts as a .csv and needs to be converted to an .xls

Thank you for your assistance.

 

[MoshiM]

Code showing error Run-time error "13", Type Mismatch

There are a couple versions of the code throughout the thread. Which one are you attempting to use?