Split data set by 500 rows help

Hi guys, I am new here and I need some help regarding a splitting rows macro


Description:
1st part: To count how many entries are filled (by column A) not counting the 1st row
2nd part: divide the number above by 500
3rd part: after divided by 500, it will be rounded up (1501/500 will be 4)
4th part: create a new excel file per 500 (4 excel file will be created if there's 1501 rows), each excel file will only have 1 tab on the bottom left instead of the original 3 when you 1st create an excel file, and the tab name in each file would be 2-500, and 501-1000 for the 2nd created excel file (1st row being the topic that will display in all the created excel file)
5th part:generate the data into the newly created files (row 2 to 500 in 1st created file, row 501-1000 in 2nd file) (row 1 of each excel file created should be the row 1 of the original document)
6th part: save each excel file on to desktop and the name will be row 2-500.xls and so forth


Do you think this will be a long project or something a noob like me can't handle

I am having trouble
how do i make it so that the division would be rounded up (aka 501/500 = 2)
I checked on google theres a roundup command, but when i input it into excel 2007, it didn't seem to work

Looks like a short project to me. Here is code you can use as an example to get parts 1-3 done.

Sub Macro1()
Dim rowCount As Long, newWBCount As Integer, k As Integer
Dim wbStartRow As Long, tmp As String
Dim wbNames() As String
ReDim wbNames(1)

    rowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) - 1
    
    newWBCount = Application.WorksheetFunction.RoundUp(rowCount / 500, 0)

    wbNames(0) = "2-500"
    
    wbStartRow = 501
    
    For k = 1 To newWBCount - 1
        ReDim Preserve wbNames(UBound(wbNames) + 1)
        wbNames(UBound(wbNames)) = CStr(wbStartRow) & "-" & CStr(wbStartRow + 499)
        wbStartRow = wbStartRow + 500
    Next
    
    For k = 0 To UBound(wbNames)
        tmp = tmp & wbNames(k) & vbCrLf
    Next
    
    MsgBox (k - 1) & " names of new workbooks to be added are: " & vbCrLf & tmp

End Sub

^Omg thank you, i was trying it out and turned out i was missing the word "Application"

Now i am stuck at the forth part , i did some research and was told to try the loop function, but i don't really get it

Refer to the For loop near the bottom of my code. If you choose to store the list of new workbook names in an array, as I have done,
then you could add the new workbooks using a For loop. I've created a list of workbook names inside the loop. You would replace that line
of code with code to add new workbooks, and the names would be those names stored in the array wbNames. In the loop, the variable k
allows you to access each element of the array, so if you calculated that you need 7 workbooks, this loop will run 7 times.
If you have over 1,000 rows, the first loop will give you the name for wbName(0), which will be "2-500", and the next time through
the loop will give you the name for wbName(1), and that name will be "501-1000", etc. The loop continues until there are no more
workbook names in the array.

I used the loop to build a list--you will use the same starting and ending line of this loop and replace the code in the middle
to add each new workbook.

I am sorry if I sound noob but wheres the loop near the bottom of your code?

For k = 0 To UBound(wbNames)
        tmp = tmp & wbNames(k) & vbCrLf
    Next

I am sorry, I am still confuse about the fourth part

i tried putting the marco in my excel, (2007) but it said debug during the
"rowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) - 1"
is it because the above is a Excel 2003 code?

Change:

rowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) - 1

to this

rowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Rows - 1

It works either way in my Excel 2003 file.