【實例06-批量修改工作表名稱】Excel表格VBA編程實例 代碼分享
Dim wbname As String
Private Sub CommandButton獲取_Click()
'獲取工作簿中包含的工作表
With ThisWorkbook.Worksheets("名稱列表")? '清除原列表數(shù)據(jù)
? ? .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
