Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Test for Worksheet (VBA Excel 2003)

    Hi,

    Im trying to script a test to see if certain worksheets exist and if they do then to print. Ive written a usual 'IF' test statement as below, but is doesnt seem to work.

    If ActiveSheet.Name = "Bob" Then
    Sheets("Bob").Select
    Call formatSheet
    ActiveSheet.PageSetup.Orientation = xlLandscape
    ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
    End If

    If ActiveSheet.Name = "Ted" Then
    Sheets("Ted").Select
    Call formatSheet
    ActiveSheet.PageSetup.Orientation = xlLandscape
    ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
    End If

    If ActiveSheet.Name = "Daisy" Then
    Sheets("Daisy").Select
    Call formatSheet
    ActiveSheet.PageSetup.Orientation = xlLandscape
    ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
    End If

    Any ideas please.

    Cheers
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test for Worksheet (VBA Excel 2003)

    You'll have to loop through all worksheets and check their names. If you encounter one you need to print, do so, then continue:

    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    Select Case wsh.Name
    Case "Bob", "Ted", "Daisy"
    ' Print this worksheet
    wsh.PageSetup.Orientation = xlLandscape
    wsh.PrintOut
    End Select
    Next wsh

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Test for Worksheet (VBA Excel 2003)

    Thanks Hans,

    In essence that works fine and is great, but I cant seem to get it to pick up the 'Call formatsheet' command, which makes the data more presentable to be printed.

    Is this possible?

    Cheers.
    Lee

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test for Worksheet (VBA Excel 2003)

    If your FormatSheet macro formats the active worksheet, implicitly or explicitly, you can activate each sheet you want to print before formatting it:

    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    Select Case wsh.Name
    Case "Bob", "Ted", "Daisy"
    ' Activate this worksheet
    wsh.Activate
    ' Format it
    Call FormatSheet
    wsh.PageSetup.Orientation = xlLandscape
    ' And print it
    wsh.PrintOut
    End Select
    Next wsh

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Test for Worksheet (VBA Excel 2003)

    Excellent. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Thanks for your help again Hans.

    Best Regards
    Lee

Posting Permissions

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