Save to new file in range using Macro

[BLuse]

Hi,

I have a file with thousands of rows. I created a macro to select 500 rows, copy past in new sheet, save as .txt and delete rows.

1) How do I increase the file name by one each time. So let's say I want to save it as Book1, Book2 etc.
2) how do I repeat it over and over again until all rows are deleted.

My Macro:
ActiveWindow.ScrollRow = 1
Range("A1:A500").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\myuser\Documents\Book1", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.SelectedSheets.Delete
ActiveWindow.ScrollRow = 1
Selection.Delete Shift:=xlUp

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long, y As Long: y = 1
    LastRow = Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 1 To LastRow Step 500
        Range("A" & x).Resize(500, 1).Copy
        Sheets.Add After:=ActiveSheet
        Cells(1, 1).PasteSpecial
        ActiveSheet.Copy
        ActiveWorkbook.SaveAs Filename:="C:\Users\myuser\Documents\Book" & y, FileFormat:=xlText, CreateBackup:=False
        ActiveWorkbook.Close False
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").Activate
        y = y + 1
    Next x
    Application.ScreenUpdating = True
End Sub

Change the sheet name (in red) to suit your needs.