VBA Copy sheet to new Excel File + automated manual data entry

please check this example and study it before asking, I will give it a bit more time when I get free, in the mean time you might check it out and try to adapt it to your needs, basically here two sheets are named copy me and copy me 2

Option Explicit

Sub TwoSheetsAndYourOut()
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet
    
    If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
    "New sheets will be pasted as values, named ranges removed" _
    , vbYesNo, "NewCopy") = vbNo Then Exit Sub

    With Application
        .ScreenUpdating = False
        
'       Copy specific sheets
'       *SET THE SHEET NAMES TO COPY BELOW*
'       Array("Sheet Name", "Another sheet name", "And Another"))
'       Sheet names go inside quotes, seperated by commas
        On Error GoTo ErrCatcher
        Sheets(Array("Copy Me", "Copy Me2")).Copy
        On Error GoTo 0
        
'       Paste sheets as values
'       Remove External Links, Hperlinks and hard-code formulas
'       Make sure A1 is selected on all sheets
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select
        
'       Remove named ranges
        For Each nm In ActiveWorkbook.Names
            nm.Delete
        Next nm
        
'       Input box to name new file
        NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
    
'       Save it with the NewName and in the same directory as original
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
        ActiveWorkbook.Close SaveChanges:=False
      
        .ScreenUpdating = True
    End With
    Exit Sub
    
ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub

• Copy the above code.
• Open any workbook.
• Press Alt + F11 to open the Visual Basic Editor (VBE).
• From the Menu, choose Insert-Module.
• Paste the code into the right-hand code window.
• Close the VBE (Alt + Q)

Option Explicit

Sub go()
    Dim NewName As String
    
    With Application
        .ScreenUpdating = False
        
        On Error GoTo ErrCatcher
        On Error GoTo 0
        
'       Input box to name new file
        NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
    
        ActiveSheet.Copy
'       Save it with the NewName and in the same directory as original
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
        ActiveWorkbook.Close SaveChanges:=False
      
        .ScreenUpdating = True
    End With
    Exit Sub
    
ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub