Exporting sheet to new workbook inside iterative loop

[Nicholasbell03]

Hi guys

First time posting here, but I've read quite a few posts on the forum so far which have helped me build a workbook to improve productivity at work.

It will take too long to fully explain what I've been building, so I'll just cut straight to the problem.
I've got a sheet called "Form" and on this sheet functions like a "mailmerge", pulling data from another sheet. I have two macros set up so far which enable me to save all iterations to pdf or to print all iterations.

I'm now trying to put together a macro which basically saves each "mailmerge" to a new excel workbook, with the road name as the title of the new file.
So only the sheet named "Form" will get saved to a new workbook for each iteration in the loop.

Sub EXCELActiveSheet()

Dim wbA As Workbook
Dim wb As Workbook
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim i As Integer


Set wbA = ThisWorkbook
Set wsA = ActiveSheet


'Get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"


'Activate sheet "Form" check range and set range for loop below
Sheets("Form").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")
    
If StartRow > EndRow Then
    Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
    MsgBox Msg, vbCritical, APPNAME
End If
    
' Iterative loop to save each iteration of sheet "Form" as a new excel file
    For i = StartRow To EndRow
        Range("ROWINDEX") = i
      
            strFile = Sheets("Form").Range("J2").Value & ".xlsm"
            strPathFile = strPath & strFile
            
            Set wb = Workbooks.Add
            wbA.Sheets("Form").Copy Before:=wb.Sheets(1)
            wb.SaveAs Filename:=strPathFile, FileFormat:=52
    Next i


End Sub

Apologies, my code may not be that elegant or efficient.

The problem I'm having is that the macro appears to work for the first iteration, thereafter it "jumps" to the first new workbook created and tries to continue the loop, which then breaks cause it can't find the data sheet to pull the data from.
At least this is what I think it happening.

Any help would be much appreciated.

Nick