VBA Array actieve sheets to PDF and PRINT

[MatthiasPBelmans]

Hoi hoi

Voor een bepaalde excel werk ik met een aantal sheets die "activeren" als een bepaalde value is geselecteerd.

Nu heb ik een code geschreven die bepaalde sheets gaat afdrukken, echter als deze sheet verborgen is zou deze niet mogen afdrukken.
de code gaat goed tot die aan de verborgen sheet komt..; dan geeft die een foutmelding.

Hoe kan ik deze het best aanpakken?

Sub testprint6()
'
' testprint6 Macro
'

'
    Sheets("WAREHOUSE INFO").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("COMMERCIAL INVOICE").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("SHIPPING ADVICE").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("END USE LETTER").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("SHIPPER DECLARATION").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("EXPORT CERT A").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Voor het maken van PDF documenten is dit ook het geval, ik heb in de array alle sheets staan die ik graag zou willen in PDF willen hebben, afhankelijk of die sheet actief is of niet.
Dit lukt dus niet.

Sub PDFActiveSheet()

'Dim wsA As Sheets
   Dim wbA     As Workbook
   Dim strTime As String
   Dim strName As String
   Dim strPath As String
   Dim strFile As String
   Dim strPathFile As String
   Dim myFile  As Variant
   On Error GoTo errHandler

   Set wbA = ActiveWorkbook
   arr = Array("commercial invoice", "shipping advice", "end use letter", "shipper declaration", "EXPORT CERT A")
   Set wsA = Sheets(arr)
   wsA.Select
   strTime = Format(Now(), "yyyymmdd\_hhmm")

   'get active workbook folder, if saved
   strPath = wbA.Path
   If strPath = "" Then
      strPath = Application.DefaultFilePath
   End If
   strPath = strPath & ""

   'replace spaces and periods in sheet name
   strName = Replace(Replace(Join(arr), " ", ""), ".", "_")

   'create default name for savng file
   strFile = strName & "_" & strTime & ".pdf"
   strPathFile = strPath & strFile

   'use can enter name and
   ' select folder for file
   myFile = Application.GetSaveAsFilename _
            (InitialFileName:=strPathFile, _
             filefilter:="PDF Files (*.pdf), *.pdf", _
             Title:="Select Folder and FileName to save")

   'export to PDF if a folder was selected
   If myFile <> "False" Then
      ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
      'confirmation message with file info
      MsgBox "PDF file has been created: " _
             & vbCrLf _
             & myFile
   End If

exitHandler:
   Exit Sub
errHandler:
   MsgBox "Could not create PDF file"
   Resume exitHandler
End Sub

Iemand ideetjes of tips om dit te aan te passen?

Mercikes

 

[snb]

Gebruik een lus.
'Opgenomen'<> 'geschreven'

Kijk vooral ook eens naar http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584

 

[MatthiasPBelmans]

Hy Snb

In afwachting van het voorgestelde boek..; zou je wat meer toelichting kunnen geven?

 

[VenA]

Met een lus

Sub VenA()
  ar = Array("WAREHOUSE INFO", "COMMERCIAL INVOICE", "SHIPPING ADVICE", "END USE LETTER", "SHIPPER DECLARATION", "EXPORT CERT A")
  For j = 0 To UBound(ar)
    If Sheets(ar(j)).Visible Then Sheets(ar(j)).PrintOut , , -4 * (j <> 0) + 1
  Next j
End Sub

 

[snb]

Sub M_snb()
  For Each it In Sheets(Array("WAREHOUSE INFO", "COMMERCIAL INVOICE", "SHIPPING ADVICE", "END USE LETTER", "SHIPPER DECLARATION", "EXPORT CERT A"))
    If it.Visible Then it.PrintOut , , 5+4 *(it.name= "WAREHOUSE INFO")
  Next
End Sub

 

[MatthiasPBelmans]

Hy tx!

kan ik dit ook voor mijn PDF verhaal gebruiken?

 

[snb]

Gewoon even aanpassen.

 

[MatthiasPBelmans]

Euhm, aanpassen waar ongeveer in deze code? Zoveel ervaring heb ik niet met VBA...

Sub PDFActiveSheet()

'Dim wsA As Sheets
   Dim wbA     As Workbook
   Dim strTime As String
   Dim strName As String
   Dim strPath As String
   Dim strFile As String
   Dim strPathFile As String
   Dim myFile  As Variant
   On Error GoTo errHandler

   Set wbA = ActiveWorkbook
   arr = Array("commercial invoice", "shipping advice", "end use letter", "shipper declaration", "EXPORT CERT A")
   Set wsA = Sheets(arr)
   wsA.Select
   strTime = Format(Now(), "yyyymmdd\_hhmm")

   'get active workbook folder, if saved
   strPath = wbA.Path
   If strPath = "" Then
      strPath = Application.DefaultFilePath
   End If
   strPath = strPath & ""

   'replace spaces and periods in sheet name
   strName = Replace(Replace(Join(arr), " ", ""), ".", "_")

   'create default name for savng file
   strFile = strName & "_" & strTime & ".pdf"
   strPathFile = strPath & strFile

   'use can enter name and
   ' select folder for file
   myFile = Application.GetSaveAsFilename _
            (InitialFileName:=strPathFile, _
             filefilter:="PDF Files (*.pdf), *.pdf", _
             Title:="Select Folder and FileName to save")

   'export to PDF if a folder was selected
   If myFile <> "False" Then
      ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
      'confirmation message with file info
      MsgBox "PDF file has been created: " _
             & vbCrLf _
             & myFile
   End If

exitHandler:
   Exit Sub
errHandler:
   MsgBox "Could not create PDF file"
   Resume exitHandler
End Sub

 

[MatthiasPBelmans]

Hoi

Kan er iemand mij hierbij nog helpen?
Ik blijf hier bij vast zitten