|
我想做一个工作表,这个工作表内的宏将打开当前文件夹内的多个工作表,在打开的每个工作表里面建立2个按钮。每个按钮指定一段宏代码,再将每个文件夹保存为启用宏的工作表。代码如下:
第一段:
Sub 工作表重命名()
Dim I
Dim Y
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim sh As Worksheet
For Each sh In Sheets
I = sh.Name
If Not I = "汇总" Then
If Not I = "Sheet1" Then
Y = Mid(I, 1, 5)
sh.Name = Y
End If
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
第二段:
Sub 合并文档()
Dim I%, Y%
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Save
For I = 3 To Sheets.Count
With Sheets(I)
If I > 3 Then .Rows("1:2").Delete
End With
Next
Sheets.Add After:=Sheets(2)
ActiveSheet.Name = "汇总表"
For Y = 4 To Sheets.Count
If Y = 4 Then
Worksheets(Y).Range("A1").CurrentRegion.Copy Worksheets("汇总表").Range("A65536").End(xlUp)
Else
Worksheets(Y).Range("A1").CurrentRegion.Copy Worksheets("汇总表").Range("A65536").End(xlUp).Offset(1)
End If
Next
ActiveWorkbook.Save
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
重新传个,之前的有时打开会有启用宏的提示。
(, 下载次数: 43)
|
|