基本上,我需要在一个PDF文件中打印11个工作表。第一工作表需要完全打印,其他10个需要基于特定单元格的条件> 0。
在我的情况下,每个工作表中都有一个数字吐入特定单元格中。一半是C16,另一半是C20(我不能将它们全部更改为完全相同,因为其他代码也依赖于此(。
是否有一种方法可以创建一个将所有这些工作表打印到具有此条件的PDF文件中的宏?
这是我尝试过的一个代码,该代码没有IF语句,因为我不知道我将在哪里实施它。SG是不需要条件的标签。由于细胞混合了哪些需要C16和C20,因此假设1-5需要C15,6-10需要C20。谢谢你!
Sub zxcv()
Sheets(Array("SG", _
"sh1", _
"sh2", _
"sh3", _
"sh4", _
"sh5", _
"sh6", _
"sh7", _
"sh8", _
"sh9", _
"sh10")).Select
Sheets("SG").Activate
ChDir "C:UsersmynamehereDownloadstest"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UsersmynamehereDownloadstesttest2.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
类似的东西应该起作用。
Sub zxcv()
Dim v, arr, vals
'sheetName:rangeAddress
arr = Array("sh1:C16", "sh2:C16", "sh3:C16", "sh4:C16", "sh5:C16", _
"sh6:C20", "sh7:C20", "sh8:C20", "sh9:C20", "sh10:C20")
Sheets("SG").Select
For Each v In arr
vals = Split(v, ":") 'split to sheet name and range address
With Sheets(vals(0))
'Add to current sheet selection?
'Replace:=False prevents de-selecting any already-selected sheet(s)
If .Range(vals(1)).Value > 0 Then .Select Replace:=False
End With
Next v
Sheets("SG").Activate
ChDir "C:UsersmynamehereDownloadstest"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UsersmynamehereDownloadstesttest2.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
不要使用选择或激活,几乎从来没有必要。如何避免在Excel VBA
中使用选择话虽如此,您首先需要用不同的单元格参考。
之后,如果符合标准,则在相同的循环中进行单元比较。
Sub zxcv()
dim elements as variant
dim sheetval as integer
for each elements in activeworkbook.sheets ' Give this a real workbook reference
if not elements.name like "SG" then
sheetval = cint(right(elements.name, len(elements.name) - (instr(1, elements.name, "sh") + 1)) ) 'Get the sheet number
if sheetval >= 6 then 'Determine which cell you need to look at
if elements.range("C20").value > 0 then 'Test value
elements.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UsersmynamehereDownloadstesttest2.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
end if
else
if elements.range("C16").value > 0 then
elements.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UsersmynamehereDownloadstesttest2.pdf",
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=_
True
end if
end if
else
elements.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UsersmynamehereDownloadstesttest2.pdf",
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=_
True
end if
next
end sub
编辑:忘了为主表提供导出。