如何从我的保存到pdf VBA宏中排除1张纸 [英] How to exclude 1 sheet from my save to pdf VBA macro

问题描述

除了我不知道如何将一张纸保存到PDF之外，我有一个运行良好的VBA代码.我想从导出中排除名为"Control"的工作表并保存为PDF.任何想法如何或在何处添加此内容?

I have a VBA code that works well, apart from the fact that i don't know how to exclude one sheet from saving to the PDF. I would like the exclude the sheet named 'Control' from being included in the export and save to PDF. Any ideas how or where i should add this?

谢谢

Sub CreatePDF()

Dim saveAsName As String
Dim WhereTo As String
Dim sFileName As String
Dim ws As Worksheet
Dim myrange

' Retrieve information from Control sheet
Sheets("Control").Activate
Range("C4").Activate
periodName = ActiveCell.Value
Range("C5").Activate
saveAsName = ActiveCell.Value
Range("C6").Activate
WhereTo = ActiveCell.Value

Set myrange = Worksheets("Control").Range("range_sheetProperties")

' Check if Stamp-field has any value at all
' if not, add the current date.
If Stamp = "" Then Stamp = Date

' Assemble the filename
     sFileName = WhereTo & saveAsName & " (" & Format(CDate(Date), "DD-MMM-YYYY") & ").pdf"

' Format all sheets as landsape, autofit to 1 page and provide header
For Each ws In ActiveWorkbook.Worksheets

    With ws.PageSetup
    Application.PrintCommunication = False
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .CenterHorizontally = True
    .ScaleWithDocHeaderFooter = False
    .AlignMarginsHeaderFooter = False
    .HeaderMargin = Application.InchesToPoints(0.31496062992126)
    Application.PrintCommunication = True

    DisplayHeader = Application.VLookup(ws.Name, myrange, 2, False)
        If Not IsError(DisplayHeader) Then
        .LeftHeader = "&L &""Arial,Bold""&11&K00-048DIVA: " & DisplayHeader
        Else: .LeftHeader = "&L &""Arial,Bold""&11&KFF0000WORKSHEET NOT DEFINED IN CONTROL SHEET "
        End If
    .CenterHeader = "&C &""Arial,Bold""&11&K00-048" & periodName
    End With

Next

' Save the File as PDF
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sFileName, Quality _
        :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

     MsgBox "PDF document has been created and saved to : " & sFileName

' Make sure we open the Control sheet upon Exit
    Sheets("Control").Activate

End Sub

推荐答案

您可以将工作表隐藏在代码的开头，然后在末尾再次显示.

You could hide the worksheet at the beginning of the code and then make it visible again at the end.

测试:

' Retrieve information from Control sheet

Sheets("Control").Visible = False

'YOUR PDF CREATION CODE

Sheets("Control").Visible = True
Sheets("Control").Activate

这篇关于如何从我的保存到pdf VBA宏中排除1张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！