使用 vba 命名新工作表时，在循环中将数字增加 1

Question

我有一个循环，它将从一个文件夹中的 3 个工作表中获取内容，并将它们粘贴到不同的工作簿上。我想把它们都命名为一个数字。新工作簿中的 1、2 和 3，供以后在操作数据时使用。

我尝试命名一个变量，但我不知道如何让它每次增加 1。

Sub find()

Dim iIndex As Integer
Dim ws As Excel.Worksheet
Dim wb      As Workbook
Dim strPath As String
Dim strFile As String
Dim i As Integer
i = 1
strPath = "P:SDSUPPORTFile Load"
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
Set wb = Workbooks.Open(Filename:=strPath & strFile)
For iIndex = 1 To wb.Worksheets.Count
Set ws = wb.Worksheets(iIndex)
'Copy each worksheet into dual sub workbook
ActiveSheet.Copy After:=Workbooks("Dual Sub.xlsm").Sheets(4)
ActiveSheet.Name = i + 1
Next iIndex
strFile = Dir 'This moves the value of strFile to the next file.
Loop

End Sub

使用此代码，它只是将第一张工作表命名为"2"，并给出一个错误，即它不能将多个工作表命名为相同的内容。我希望每张纸都命名为 1、2 和 3。

Answer 1

如果我理解你的逻辑，这应该可以解决问题：

Option Explicit
Sub find()
Dim ws As Worksheet
Dim wb As Workbook
Dim Masterwb As Workbook
Set Masterwb = Workbooks("Dual Sub.xlsm")
Dim strPath As String
Dim strFile As String
strPath = "P:SDSUPPORTFile Load"
strFile = Dir(strPath & "*.xls")
Dim i As Long
i = i + 1
Do While strFile <> ""
Set wb = Workbooks.Open(Filename:=strPath & strFile)
For Each ws In wb.Worksheets 'better to loop like this (you loop trhough every item in the workbooks.worksheets collection)
ws.Copy After:=Masterwb.Sheets(Masterwb.Sheets.Count) 'copy the worksheet on the new workbook to the last index on the master workbook
Masterwb.Sheets(Masterwb.Sheets.Count).Name = i 'name the last sheet on the master workbook the value of i starting from 1
i = i + 1
Next ws
strFile = Dir 'This moves the value of strFile to the next file.
Loop
End Sub