Macro to copy and paste changing number of rows

Try this Macro out

Sub CopyToNewSheet()
Dim WS As Worksheet

i = 1
Start = 1
With Worksheets("CRC_OpsScorecard_Macro")
    Do Until i > .Range("A1000").End(xlUp).Row
        Set WS = Sheets.Add
        WS.Name = .Range("A" & i).Value
        
        Do Until .Range("A" & i).Value = "Period to Date"
            i = i + 1
        Loop
        
        .Range("A" & Start & ":A" & i).EntireRow.Copy Destination:=WS.Range("A1")
        Start = i + 1
        i = i + 1
    Loop
End With

End Sub