vba - 仅复制粘贴工作表 'name'

Question

我需要将当前工作簿中的所有工作表名称复制粘贴到具有相同工作表名称的新工作簿中。 (没有数据。我只需要工作表名称。)

我尝试遵循 VBA 但它显示错误

"The name is already taken." (Runtime Error 1004)

'Create new work book for Pivot
Dim Source As Workbook
Dim Pivot As Workbook
Set Source = ActiveWorkbook
Set Pivot = Workbooks.Add

Dim ws As Worksheet
For Each ws In Worksheets
    'Create new worksheet in new excel
    Dim Line As String
    Line = ActiveSheet.Name
    Pivot.Activate
    Sheets.Add
    ActiveSheet.Name = Line
    Source.Activate
Next

Answer 1

你从不使用 ws所以Line永不改变。还有you do not need to select or activate anything .最后，你应该限定你的 Worksheets等与他们来自的工作簿。

Dim Source As Workbook
Dim Pivot As Workbook
Set Source = ActiveWorkbook
Set Pivot = Workbooks.Add

Dim ws As Worksheet
For Each ws In Source.Worksheets
    Pivot.Worksheets.Add.Name = ws.Name
Next

这不会保护您免受新工作簿中已经有一个名称与您的工作表名称之一相同的工作表(例如 Sheet1 )的情况，并且默认情况下它将保留新工作簿具有的所有工作表(由 Application.SheetsInNewWorkbook 属性)。