【实例06-批量修改工作表名称】Excel表格VBA编程实例 代码分享

Dim wbname As String

Private Sub CommandButton获取_Click()

'获取工作簿中包含的工作表

With ThisWorkbook.Worksheets("名称列表")  '清除原列表数据

    .Columns(1).ClearFormats

    .Columns(1).ClearContents

    .Columns(2).ClearFormats

    .Columns(2).ClearContents

End With

With ThisWorkbook.Worksheets("操作界面")

If .Cells(2, "C").Value <> "" Then

    wbname = .Cells(2, "C").Value

Else

MsgBox "请输入工作簿名称（包含扩展名）"

Exit Sub

End If

End With

Dim i As Integer

For i = 1 To Workbooks(wbname).Worksheets.Count

    ThisWorkbook.Worksheets("名称列表").Cells(i, 1).Value = Workbooks(wbname).Worksheets(i).Name

Next i

ThisWorkbook.Worksheets("名称列表").Activate

End Sub

Private Sub CommandButton修改_Click()

With ThisWorkbook.Worksheets("操作界面")

If .Cells(2, "C").Value <> "" Then

    wbname = .Cells(2, "C").Value

Else

MsgBox "请输入工作簿名称（包含扩展名）"

Exit Sub

End If

End With

With ThisWorkbook.Worksheets("名称列表")

    Dim i As Long

    Dim imax As Long

    imax = .Cells(1000000, 1).End(xlUp).Row

    For i = 1 To imax

        If .Cells(i, 1).Value <> "" And .Cells(i, 2).Value <> "" Then

            Workbooks(wbname).Worksheets(CStr(.Cells(i, 1).Value)).Name = CStr(.Cells(i, 2).Value)

        End If

    Next i

    Workbooks(wbname).Save

    MsgBox "处理完成"

End With

End Sub