Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    look for sheets existence if there add to array (xls 2003)

    Hi
    I am trying to get a peice of code to look for 4 worksheets " coil summary "," coil summary 2 "," coil summary 3 "," coil summary 4 " in a workbook.
    any sheet that exist is added to an array list to be slected.
    If any of the worksheets dont exist the they are not added to the array which is a list of sheets to print.

    The sheets are always in the order of " coil summary "," coil summary 2 "," coil summary 3 "," coil summary 4 " if coil summary 2 does not exist then 3 cannot and 4 cannot.

    Below is my attempt which seems to be weak and rather long.

    ' Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next

    Set x = ActiveWorkbook.Sheets("Coil Summary 4")
    If Err = 0 Then SheetExists = True _
    'Sheets(Array("Coil Summary","Coil Summary 2","Coil Summary 3","Coil Summary 4")).Select
    Else: SheetExists = False
    Set x = ActiveWorkbook.Sheets("Coil Summary 3")
    If Err = 0 Then SheetExists = True _
    'Sheets(Array("Coil Summary","Coil Summary 2","Coil Summary 3")).Select
    Else: SheetExists = False
    Set x = ActiveWorkbook.Sheets("Coil Summary 2")
    If Err = 0 Then SheetExists = True _
    'Sheets(Array("Coil Summary","Coil Summary 2")).Select
    Else: SheetExists = False
    Set x = ActiveWorkbook.Sheets("Coil Summary")
    If Err = 0 Then SheetExists = True _
    'Sheets(Array("Coil Summary")).Select
    End If
    End If
    End If
    End If

    Sheets(Array("Coil Summary")).Select

    ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="printer 1 on lpt1"

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    Do you like this better:
    <pre>Option Explicit
    '
    Sub Macro1()
    MsgBox SheetExists()
    End Sub
    '
    Function SheetExists() As Boolean
    Dim i As Integer, s As String, ws As Worksheet, v As Variant, n As Integer
    n = 0
    ReDim v(0 To 0)
    For i = 1 To 4
    If i = 1 Then
    s = "Coil Summary"
    Else
    s = "Coil Summary " & i
    End If
    For Each ws In Worksheets
    If ws.Name = s Then
    ReDim Preserve v(0 To n)
    v(n) = s
    n = n + 1
    End If
    Next ws
    Next i
    If v(0) <> "" Then
    Worksheets(v).Select
    SheetExists = True
    Else
    SheetExists = False
    End If
    End Function
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: look for sheets existence if there add to array (xls 2003)

    First a question. Are the sheet names " coil summary "," coil summary 2 "," coil summary 3 "," coil summary 4 " with a space before and after the name and lower case "c" and "s" or are they "Coil Summary", "Coil Summary 2", "Coil Summary 3", "Coil Summary 4" with no spaces and upper case "C" and "S"? You specify differently in the text and in the code.

    The code below assumes they are named like in your code:

    <code>
    Public Sub SelSheets()
    Dim strName As Variant
    Dim strNmArray() As String, iCnt As Integer
    Dim oSH As Worksheet
    iCnt = 0
    For Each strName In Array("Coil Summary", "Coil Summary 2", "Coil Summary 3", "Coil Summary 4")
    Set oSH = Nothing
    On Error Resume Next
    Set oSH = Sheets(strName)
    On Error GoTo 0
    If Not oSH Is Nothing Then
    If iCnt = 0 Then
    ReDim strNmArray(0 To 0)
    Else
    ReDim Preserve strNmArray(0 To iCnt)
    End If
    strNmArray(iCnt) = strName
    iCnt = iCnt + 1
    End If
    Next strName
    If iCnt > 0 Then
    Sheets(strNmArray()).Select
    End If
    End Sub
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    Hi Legare,

    Yes the C and S in Coil Summary are capitalized.

    no spaces on either sides "Coil Summary","Coil Summary 2" and so on

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: look for sheets existence if there add to array (xls 2003)

    Then the code I just added to the post should do what you want.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    i added your code to my module removing Public Sub SelSheets() and end sub so that the code could be ran in my code

    and get the error 1004 select method of sheets class failed

    is there someting removed

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    how do i make your code able to mesh right in to the macro i would like use it in??

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    Just replace 'Sheets(Array("Coil Summary")).Select
    with
    SelSheets
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    But if you don't have any of the sheets in the workbook, then you need a function like mine. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <pre>Option Explicit
    '
    Sub Coilchartspdf()

    'Declare varibles
    Dim strPath As String
    Dim strFile As String
    Dim vsavename
    Dim vcompanyshort
    Dim vLocation
    Dim vLocationFile
    Dim vFormation
    Dim vServiceOrderNumber

    'Define Varibles


    vcompanyshort = Worksheets("INPUT").Range("CompanyShort").Value

    vLocation = Worksheets("INPUT").Range("Location").Value

    vLocationFile = Worksheets("INPUT").Range("Location").Value

    vFormation = Worksheets("INPUT").Range("Formation").Value

    vServiceOrderNumber = Worksheets("INPUT").Range("ServiceOrderNumber").Va lue





    'ChDir ("G:Customer 2006" & vcompanyshort & "" & vLocation & "" & vServiceOrderNumber)

    'MkDir (vServiceOrderNumber)
    'ChDir ("G:Customer 2006" & vcompanyshort & "" & vLocation & "" & vServiceOrderNumber)

    'Directory to look for company folder

    'Application.ScreenUpdating = False

    'Dim MyData As DataObject

    Set MyData = New DataObject
    MyData.SetText vsavename
    MyData.PutInClipboard
    'Sheets(Array("Coil Summary")).Select
    If SheetExists Then _
    ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="Acrobat PDFWriter on LPT1:"
    End Sub
    '
    Function SheetExists() As Boolean
    Dim i As Integer, s As String, ws As Worksheet, v As Variant, n As Integer
    n = 0
    ReDim v(0 To 0)
    For i = 1 To 4
    If i = 1 Then
    s = "Coil Summary"
    Else
    s = "Coil Summary " & i
    End If
    For Each ws In Worksheets
    If ws.Name = s Then
    ReDim Preserve v(0 To n)
    v(n) = s
    n = n + 1
    End If
    Next ws
    Next i
    If v(0) <> "" Then
    Worksheets(v).Select
    SheetExists = True
    Else
    SheetExists = False
    End If
    End Function
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    get the error 1004 select method of sheets class failed
    occures at this line in the function: Worksheets(v).Select
    If v(0) <> "" Then

    Worksheets(v).Select

    SheetExists = True Else
    SheetExists = False
    End If

    Wht would cause this error>?

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: look for sheets existence if there add to array (xls 2003)

    Its hard to say without seeing the rest of your code. Could you upload a workbook that shows the prolem?
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    sure

  13. #13
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: look for sheets existence if there add to array (xls 2003)

    this workbook is using the code you gave me

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: look for sheets existence if there add to array (xls 2003)

    The code is failing because you have a hidden sheet named "Coil Summary 3" which matches one of the sheets you want to select. Hidden sheets can not be selected. What do you want to happen if the code finds a sheet that matches one of the names that is hidden? It could either unhide the sheet and select it, or skip it and not put it in the list.
    Legare Coleman

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: look for sheets existence if there add to array (xls 2003)

    Same problem as my previous reply.
    Legare Coleman

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
  •