Option Explicit
Sub 筛选()
'
' 筛选 宏
'
' 快捷键: Ctrl+f
'
'筛选 宏自动生成
Sheets("小型建筑表 (打印)").Select
Rows("5:5").Select
Selection.AutoFilter
Range("F5").Select
ActiveSheet.Range("$A$5:$SW$184").AutoFilter Field:=6, Criteria1:="<>0", _
Operator:=xlOr
ActiveSheet.Range("a6").Resize(173) = "=subtotal(103,$b$6:b6)" '对筛选出的数据进行编号
'设置页边距
Dim sh As Worksheet
For Each sh In Worksheets
With sh.PageSetup
.CenterHorizontally = True '页边水平
.CenterVertically = False '页边垂直
.LeftMargin = Application.CentimetersToPoints(1.8) '左边距
.RightMargin = Application.CentimetersToPoints(1.8) '右边距
.TopMargin = Application.CentimetersToPoints(2.5) '上边距
.BottomMargin = Application.CentimetersToPoints(2.5) '下边距
.FooterMargin = Application.CentimetersToPoints(1.3) '页眉
.HeaderMargin = Application.CentimetersToPoints(1.3) '页脚
End With
Next sh
ThisWorkbook.Save '保存文档
Sheets(Array("表一", "小型建筑表 (打印)")).Select '选中需要打印的sheet
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF" '打印成PDF并保存
ThisWorkbook.Save '保存文档
End Sub
'
' 筛选 宏
'
' 快捷键: Ctrl+f
'
'筛选 宏自动生成
Sheets("小型建筑表 (打印)").Rows("5:5").AutoFilter
ActiveSheet.Range("$A$5:$SW$184").AutoFilter Field:=6, Criteria1:="<>0", _
Operator:=xlOr
ActiveSheet.Range("a6").Resize(173) = "=subtotal(103,$b$6:b6)" '对筛选出的数据进行编号
'设置页边距
Dim sh As Worksheet
For Each sh In Worksheets
With sh.PageSetup
.CenterHorizontally = True '页边水平
.CenterVertically = False '页边垂直
.LeftMargin = Application.CentimetersToPoints(1.8) '左边距
.RightMargin = Application.CentimetersToPoints(1.8) '右边距
.TopMargin = Application.CentimetersToPoints(2.5) '上边距
.BottomMargin = Application.CentimetersToPoints(2.5) '下边距
.FooterMargin = Application.CentimetersToPoints(1.3) '页眉
.HeaderMargin = Application.CentimetersToPoints(1.3) '页脚
End With
Next sh
ThisWorkbook.Save '保存文档
Sheets(Array("表一", "小型建筑表 (打印)")).Select '选中需要打印的sheet
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF" '打印成PDF并保存
ThisWorkbook.Save '保存文档
End Sub
貌似上面的部分可以简化一下，VBA代码一般不需要先选择对象，直接写方法的话速度能快些。
追问额，为啥修改之后，CTRL+F 启动宏的快捷键不能用了
追答不能吧，那就Alt+F8，选择宏--选项，重新设置Ctrl+F为快捷键
追问我晕了，，，我自己的错，，还有，我想问个，，我想在PERSONAL.XLSB中添加关闭其他任何打开的excel都不会弹出保存对话框，而是自动保存并关闭excel，该怎么实现，网上找了好多都实现不了。
追答施主又调皮了不是。用下面这句，保存并关闭活动工作簿
Sub SaveClose()ActiveWorkbook.Close SaveChanges:=True
End Sub
