Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deselect single sheet (Excel XP)

    OK, OK,

    I'm getting the fun of programming in VBA. I've made the following code:

    Sub KnopMakePDF()
    '
    ' KnopMakePDF Macro

    Sheets().Select
    Sheets("Voorkant").Activate
    Application.ActivePrinter = "Acrobat Distiller op Ne00:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "Acrobat Distiller op Ne00:", Collate:=True
    End Sub

    As you can see, i've selected all the sheets for printing to Adobe Acrobat. But in this selection I want one sheet not to be printed. So I have to add some coding to deselect the sheet named "Berekening".

    Sheets("Berekening").Deselect is propably to simple, but it's not working


    Does anyone have an idea?
    TNX

    Gerben

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deselect single sheet (Excel XP)

    <P ID="edit" class=small>(Edited by JohnBF on 04-Aug-03 12:10. Code corrections.)</P>Gerben, if you can find a .Deselect Method, more power to you. Try:

    Sub KnopMakePDF()
    Dim wsSheet As Sheet
    For Each wsSheet In ActiveWindow.SelectedSheets
    If wsSheet.Name <> "Berekening" Then _
    wsSheet.PrintOut Copies:=1, ActivePrinter:="Acrobat Distiller op Ne00:", Collate:=True
    Next wsSheet
    Sheets("Voorkant").Activate
    End Sub

    You may need to build some error-trapping in because of the hard coding of Sheet names.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect single sheet (Excel XP)

    John,

    I a big newbie on this area. Do I have to change anything in your provided coding. I'm getting an error message on "Dim wsSheet As Sheet". The error message says:
    A userdefined datatype is not defined by the user (translated from the dutch error message ;-))

    tnx
    Gerben

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deselect single sheet (Excel XP)

    My error, it should be

    Dim wsSheet as Worksheet
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect single sheet (Excel XP)

    John,

    I've added one moren rule: sheets().select so the total code will be:


    Dim wsSheet As Worksheet
    Sheets().Select
    For Each wsSheet In ActiveWindow.SelectedSheets
    If wsSheet.Name <> "Berekening" Then _
    wsSheet.PrintOut Copies:=1, ActivePrinter:="Acrobat Distiller op Ne00:", Collate:=True
    Next wsSheet
    Sheets("Voorkant").Activate
    End Sub

    Only now, all the sheets are being printed individually, not as one printjob. So now i get 15 seperate printouts instead of one. And because i'm printing towards Acrobat reader i get 15 different files instead of one.

    Gerben

  6. #6
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect single sheet (Excel XP)

    Can't i do something like:

    Sheets().select <> sheets("Berekening")

    just to select all the sheets (without naming then individually) exept the sheet Berekening

    Gerben

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deselect single sheet (Excel XP)

    Why not just select ALL But the one:
    Steve

    <pre>Option Explicit
    Sub SelectAllBut1()
    Dim x As Integer
    Dim sht
    Dim sArray()
    ReDim sArray(1 To Sheets.Count)
    x = 0
    For Each sht In Sheets
    If sht.Name <> "BEREKENING" Then
    x = x + 1
    sArray(x) = sht.Name
    End If
    Next sht
    ReDim Preserve sArray(1 To x)
    Sheets(sArray).Select
    End Sub</pre>


  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deselect single sheet (Excel XP)

    I agree that 's what he wants to do, Steve, and this one is a bit over my head. Here's what I have, and it works in XL 2000 up:

    Sub PrintAllBut()
    Dim strSheetNames As String
    Dim intC As Integer
    With ActiveWorkbook
    For intC = 1 To .Worksheets.Count
    If .Worksheets(intC).Name <> "Berekening" Then _
    strSheetNames = strSheetNames & .Worksheets(intC).Name & ", "
    Next intC
    strSheetNames = Left(strSheetNames, Len(strSheetNames) - 2)
    Worksheets(Split(strSheetNames, ", ")).PrintOut _
    Copies:=1, ActivePrinter:="Acrobat Distiller op Ne00:", Collate:=True
    Sheets("Voorkant").Activate
    End With
    End Sub

    Note that in Split(strSheetNames, ", ") there is a space after that comma within the quotes.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Lounger
    Join Date
    Jul 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect single sheet (Excel XP)

    John & Steve,

    I used the code John provided me, and it's working perfectly.

    Thanks again.

    P.S. Do you know a good website from wich I can learn the essentials of programming in VBA?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deselect single sheet (Excel XP)

    Here is good tutorial
    VBA Basics

    I also get a lot of excel/VBA info from:
    Chip Pearson
    John Walkenbach

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect single sheet (Excel XP)

    There is a "deselect" method, only it's called <font face="Georgia">filter:

    NewArray=Filter(SourceArray, FilterString[, Switch[, Compare]])</font face=georgia>

    FilterString is what you're looking for.
    <font face="Georgia">Switch</font face=georgia> is boolean: <font face="Georgia">True</font face=georgia> will get you an array of what's in the FilterString, <font face="Georgia">False</font face=georgia> will <font face="Georgia"> "Deselect"</font face=georgia> FilterString values from the array.

    I need to do this on a daily basis, so I 1) Build an array of all "printable" worksheets, 2) Filter the ones I don't want to print and 3) Select this filtered array to PrintOut.

    <font face="Georgia">Sub PrinterOuter()
    Dim FirstSheet As Integer, SheetsArray() As String, SheetCount As Integer

    ReDim SheetsArray(Sheets.Count - 1)

    'Build an array of all worksheets. This FOR loop works for any number of sheets
    For FirstSheet = 1 To Sheets.Count <font color=448800>'Use Minus 1 since the Worksheet numbers are zero based </font color=448800>
    SheetsArray(FirstSheet - 1) = Sheets(FirstSheet).Name <font color=448800>'i.e. the first worksheet is #0, not #1</font color=448800>
    Next FirstSheet

    'Filter out "Sheet2"</font color=448800>
    SheetsArray = Filter(SheetsArray, "Sheet2", False, vbTextCompare)

    'Select and print out
    Sheets(SheetsArray).Select
    ActiveWindow.SelectedSheets.PrintOut
    End Sub</font face=georgia>

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deselect single sheet (Excel XP)

    Very nice! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> BTW, you can boil your last three lines down to:

    Sheets(Filter(SheetsArray, "Sheet2", False, vbTextCompare)).PrintOut

    and "SheetCount" is an unused Variable. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deselect single sheet (Excel XP)

    I can see the advantage of using the FILTER to remove items from an array that already exists, but I don't see the advantage of creating an array in VB using ALL the items, and then remove the item you don NOT want from the full list.

    Why not just create the array originally ONLY with the items of interest: that is do NOT even add it to the list as I did in <post#=281408>post 281408</post#>?

    Steve

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deselect single sheet (Excel XP)

    The end result is the same.

    There's also a small gotcha in the filter method that I discovered later. If you filter on a set of characters which appear in multiple sheet names, they all get filtered out. E.g., with sheets called AAASummary, AAAData1 & AAAData2, if you filter AAA, all three will be removed.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deselect single sheet (Excel XP)

    Steve,
    This particular procedure will work with a workbook with any number of sheets, and remove that "Sheet2". I just set up an example. Sure, you can hardwire the procedure any way you want. Gerben started this discussion with a question about "Deselect". I proposed using Filter to "Deselect.

    And as with nearly every other VBA discussion, you get about 150 ways to skin the cat. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>


    John,
    I guess it depends on how much you use the Filer method. I've only really used it for this one deal. Could be a Gotcha, or maybe you don't care about the other sheets, and only want those "AAA..." ones. But good to know, nonetheless! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Errol

Page 1 of 2 12 LastLast

Posting Permissions

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