Consulting

Results 1 to 7 of 7

Thread: VBA Code for PDF of Vibile Sheets in array

  1. #1

    VBA Code for PDF of Vibile Sheets in array

    Hello, i am trying to run a macro that PDFs an array of sheets in the description. However i have a macro that hides certain sheets pending other options and when i do the printout option it comes up with an error if one of the sheets has been hidden. The sheets need to remain hidden depending on the options.

    The code i currently have is below.

    Sub PrintSpecificSheets()
    ThisWorkbook.Worksheets(Array("D 30%", "EW 30%", "RW 30%", "S 30%", "W 30%")).PrintOut
    End Sub
    Is there an option i can include that only PDFs the visible sheets out of whats included in the above array?

    It would be perfect if the PDF of the visible sheets is combined of all sheets in the array that are visible and visible only.
    Last edited by Aussiebear; 11-20-2022 at 08:59 PM. Reason: Added code tags to supplied code

  2. #2
    VBAX Regular
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    57
    Location
    I expect you need to dynamically build the array with only visible sheets.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Is there any code that can search the array and pick out the visible sheets in the array to then only print them combiend.

    The array is large and cant be updated each time to print.

  4. #4
    VBAX Regular
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    57
    Location
    You show only 5 elements in array. That is not large. How many worksheets are there?

    Would have to test if each sheet is visible and conditionally build array string.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    This code is to be applied to multiple worksheets with different arrays.

  6. #6
    VBAX Regular
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    57
    Location
    Doesn't alter my comments.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    894
    Location
    Quote Originally Posted by Jimmcintyre View Post
    Is there any code that can search the array and pick out the visible sheets in the array to then only print them combiend.

    The array is large and cant be updated each time to print.

    Sub PrintSpecificSheets()
        Dim wsArr As Variant, wsC As Integer, z As Integer, oVar() As Variant
    
        wsArr = Array("D 30%", "EW 30%", "RW 30%", "S 30%", "W 30%")
    
        For wsC = 0 To UBound(wsArr)
            If Sheets(wsArr(wsC)).Visible Then
                ReDim Preserve oVar(z): oVar(z) = wsArr(wsC): z = z + 1
            End If
        Next wsC
    
        Worksheets(oVar).PrintOut
    End Sub
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •