導(dǎo)讀打開(kāi)目標(biāo)工作簿同按ALTF11這兩個(gè)鍵，我來(lái)為大家科普一下關(guān)于excel工作表統(tǒng)計(jì)技巧?下面希望有你要的答案，我們一起來(lái)看看吧!excel工作表統(tǒng)計(jì)技巧打開(kāi)....

打開(kāi)目標(biāo)工作簿同按 ALT F11 這兩個(gè)鍵，我來(lái)為大家科普一下關(guān)于excel工作表統(tǒng)計(jì)技巧?下面希望有你要的答案，我們一起來(lái)看看吧!

excel工作表統(tǒng)計(jì)技巧

打開(kāi)目標(biāo)工作簿

同按 ALT F11 這兩個(gè)鍵

在彈出窗口的菜單上點(diǎn)選：插入--模塊

在右邊編輯窗口中粘貼以下代碼

Sub mulu()

On Error GoTo R 如遇到錯(cuò)誤語(yǔ)句將直接挑到R運(yùn)行

Dim I As Integer 定義I,shtcount和SelectionCell變量

Dim ShtCount As Integer

Dim SelectionCell As Range

ShtCount = Worksheets.Count

If ShtCount = 0 Or ShtCount = 1 Then Exit Sub 工作簿內(nèi)僅0或1張工作表時(shí)候退出

Application.ScreenUpdating = False 取消刷新屏幕以便加快運(yùn)行代碼

For I = 1 To ShtCount 遍歷所有工作表

If Sheets(I).Name = "目錄" Then 當(dāng)?shù)谝粡埞ぷ鞅砻麨椤蹦夸洝睍r(shí)候，將該表置前

Sheets("目錄").Move Before:=Sheets(1)

End If

Next I

If Sheets(1).Name <> "目錄" Then 當(dāng)?shù)谝粡埞ぷ鞅砻粸椤蹦夸洝睍r(shí)候.生成”目錄”

ShtCount = ShtCount 1

Sheets(1).Select

Sheets.Add

Sheets(1).Name = "目錄"

End If

Sheets("目錄").Select 選定工作表”目錄”

Columns("B:B").Delete Shift:=xlToLeft 清除B列

Application.StatusBar = "正在生成目錄…………請(qǐng)等待！" 添加等待狀態(tài)條

For I = 2 To ShtCount 遍歷除第一張工作表外所有工作表,建立鏈接

ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目錄").Cells(I, 2), Address:="", SubAddress:= _

"" & Sheets(I).Name & "!R1C1", TextToDisplay:=Sheets(I).Name

Next

Sheets("目錄").Select

Columns("B:B").AutoFit 自動(dòng)調(diào)整

Cells(1, 2) = "目錄" B列第一個(gè)單元格錄“目錄”

Set SelectionCell = Worksheets("目錄").Range("B1")

With SelectionCell 調(diào)整單元格格式

.HorizontalAlignment = xlDistributed

.VerticalAlignment = xlCenter

.AddIndent = True

.Font.Bold = True

.Interior.ColorIndex = 34

End With

Application.StatusBar = False

Application.ScreenUpdating = True

R:

End Sub

按下 F5 鍵后會(huì)在工作簿最前面插入一個(gè)目錄工作表。