前輩們好,
第一次寫程式
需求大概是抓取總檔內的某些資料到範本內並依照編號產生新的工作表，東拼西湊弄了一陣子還是有點問題...
程式碼如下
想請教在新增活頁簿和移動工作表到活頁簿的時候如何使用變數去操作
請前輩們指點迷津...感謝

Sub 自動複製()

Dim X1 As Integer, X2 As Integer

X1 = InputBox("開始欄位")
X2 = InputBox("結束欄位")

Sheets("自動新增").Select '選本範本工作表'
Range("A1:K11").Select '選取內容'
Selection.Copy '複製選取內容'
    For a = X1 To X2   '新增工作表(輸入的範圍)'
        Sheets.Add after:=Sheets(Sheets.Count) '新增工作表在最後面'
        Sheets(Sheets.Count).Name = Sheets(1).Cells(a, "B") & Sheets(1).Cells(a, "F") '更改工作表名稱為B欄+F欄的內容'
        ActiveSheet.Paste '貼上內容'
        Range("A3").Value = "編號：" & Sheets(1).Cells(a, "E") & "     姓名：" & Sheets(1).Cells(a, "F") & "     時數：" & Sheets(1).Cells(a, "J") & "小時" 'A3的值為E欄、F欄、J欄'
        Range("A4").Value = "電話：" & Sheets(1).Cells(a, "V") & "     住址：" & Sheets(1).Cells(a, "U") 'A4的值為V欄、U欄'
        Range("A4").Select '將A4的分行改為空白鍵'
            Cells.Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart, SearchOrder _
             :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     
    Next
    
    Set NewBook = Workbooks.Add '新增活頁簿命名為test'
        With NewBook
        .SaveAs Filename:="test.xlsx"
    End With

    
    For b = X1 To X2 '移動建立好的工作表到test活頁簿'
        Windows("測試-0505更新V1.xlsm").Activate
        Sheets(Array(Sheets(1).Cells(b, "B") & Sheets(1).Cells(b, "F"))).Select
        Application.CutCopyMode = False
        Sheets(Array(Sheets(1).Cells(b, "B") & Sheets(1).Cells(b, "F"))).Move after:=Workbooks("test.xlsx"). _
        Sheets(1)
        Windows("測試-0505更新V1.xlsm").Activate
    Next
    
    Windows("test.xlsx").Activate

Dim xResult As VbMsgBoxResult '工作表重新排序'
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
Application.DisplayAlerts = False '刪除預設的工作表'
Sheets("工作表1").Delete
Application.DisplayAlerts = True
End Sub