我有两个问题,但其中一个我绝对无法找到直接答案,那就是我是否可以循环遍历工作表并将打印设置应用于某些工作表而不激活它们?似乎激活工作表会拖慢运行代码的时间,并且不受欢迎,但我找不到解决打印设置的方法。(我知道我也不应该“选择”,我在本代码中也这样做了)。
我的第二个问题是,当我运行下面的 IF THEN 语句时,它没有排除列出的三个工作表(“LAS 报告”、“标准”和“图例”)。我做错了什么?
Dim i As Integer
Dim wsh As Worksheet
For i = 1 To Application.Sheets.Count
Sheets(i).Activate
If wsh.Name <> "LAS Report" And wsh.Name <> "Criteria" And wsh.Name <> "Legend" Then
With ActiveSheet
.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
.PageSetup.RightMargin = Application.InchesToPoints(0.25)
.PageSetup.TopMargin = Application.InchesToPoints(0.5)
.PageSetup.BottomMargin = Application.InchesToPoints(0.5)
.PageSetup.HeaderMargin = Application.InchesToPoints(0.3)
.PageSetup.FooterMargin = Application.InchesToPoints(0.3)
.PageSetup.Orientation = xlLandscape
.PageSetup.PaperSize = xlPaperLetter
.PageSetup.ScaleWithDocHeaderFooter = True
.PageSetup.AlignMarginsHeaderFooter = True
.PageSetup.Zoom = False
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = False
.PageSetup.PrintGridlines = True
.PageSetup.PrintTitleRows = "$1:$1"
.PageSetup.CenterHeader = "&A &D PAGE &P of &N"
.Cells.EntireColumn.AutoFit
.Columns("C:C").ColumnWidth = 25
.Columns("D:D").ColumnWidth = 50
.Columns("G:G").ColumnWidth = 19
.Columns("N:N").ColumnWidth = 5
.Columns("O:O").ColumnWidth = 7
.Columns("Q:Q").ColumnWidth = 11
.Columns("E:E").Hidden = True
.Columns("I:J").Hidden = True
.Columns("Q:Q").Hidden = True
.Cells.EntireColumn.WrapText = True
.Columns("A:A").Select
End With
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.6
End With
End If
Range("A1").Select
Next i
答案1
它的写法存在一些问题。例如,你永远不会设置wsh
任何东西,因此长If
语句毫无意义。请在开头尝试这样做:
Dim i As Integer
Dim wsh As Worksheet
For i = 1 To Application.Sheets.Count
Set wsh = Sheets(i)
If wsh.Name <> "LAS Report" And wsh.Name <> "Criteria" And wsh.Name <> "Legend" Then
With wsh
...
接近结尾处是这样的:
...
.Columns("Q:Q").Hidden = True
.Cells.EntireColumn.WrapText = True
With .Columns("A:A")
.FormatConditions.AddUniqueValues
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).DupeUnique = xlDuplicate
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.6
End With
End With
End With
End If
Next i
我没有测试过这个。基本前提是永远不要激活或选择任何东西。这意味着你不要用ActiveSheet
或来引用它Selection
。这也应该会加快代码的运行速度。
唯一一件它做不到而原来能做到的事情是A1
在每张工作表上进行选择,因为如果不激活工作表则无法做到这一点。