Combining 2 columns in different spreadsheets and creating all combinations

Put the "STATES" and "PRESIDENTS" in same workbook, then it will create a new sheets call "RESULT"

Sub RepeatName()

Dim state_name, president_name As String
Dim state_lstCol, president_lstCol As Long
Application.ScreenUpdating = False

Sheets("STATES").Select
state_lstCol = Range("A" & Rows.count).End(xlUp).Row
Sheets("PRESIDENTS").Select
president_lstCol = Range("A" & Rows.count).End(xlUp).Row

Sheets.Add
ActiveSheet.Name = "RESULT"
For i = 2 To state_lstCol
    Sheets("STATES").Select
    state_name = Range("A" & i).Value
    
        For j = 2 To president_lstCol
            Sheets("PRESIDENTS").Select
            president_name = Range("B" & j).Value
            
            Sheets("RESULT").Select
            ActiveCell.Formula = state_name & president_name
            ActiveCell.Offset(1, 0).Select
        Next j
Next i
Application.ScreenUpdating = True
End Sub

=INDEX(STATES!$A$2:$A$57,ROUNDUP(ROW(A1)/(COUNTA(PRESIDENTS!B:B)-1),0))&INDEX(PRESIDENTS!$B$2:$B$44,MOD(ROW(A1)+ROUNDUP(ROW(A1)/(COUNTA(PRESIDENTS!B:B)-1),0)-1,COUNTA(PRESIDENTS!B:B)))

If you want use function, here you are.
Need put "STATES" and "PRESIDENTS" in same workbook.

Is there a way that if i hit the row limit in excel 2010 to continue adding them into a new worksheet?

Excel 2010 Worksheet size 1,048,576 rows by 16,384 columns

Sub RepeatName()

Dim state_name, president_name As String
Dim state_lstCol, president_lstCol As Long
Dim i, j, k As Integer
k = 1
Application.ScreenUpdating = False

Sheets("STATES").Select
state_lstCol = Range("A" & Rows.count).End(xlUp).Row
Sheets("PRESIDENTS").Select
president_lstCol = Range("B" & Rows.count).End(xlUp).Row

Sheets.Add
ActiveSheet.Name = "RESULT" & k
For i = 2 To state_lstCol
    Sheets("STATES").Select
    state_name = Range("A" & i).Value
    
        For j = 2 To president_lstCol
            Sheets("PRESIDENTS").Select
            president_name = Range("B" & j).Value
            
            Sheets("RESULT" & k).Select
            ActiveCell.Formula = state_name
            ActiveCell.Offset(0, 1).Formula = president_name
            ActiveCell.Offset(1, 0).Select
            If ActiveCell.Row = 1048576 Then
                    Sheets.Add
                    k = k + 1
                    ActiveSheet.Name = "RESULT" & k
            End If
        Next j
Next i
Application.ScreenUpdating = True
End Sub

What if i wanted to not combine them but have them in separate columns?

 ActiveCell.Formula = state_name
 ActiveCell.Offset(0, 1).Formula = president_name