Array to pull out only visible worksheets

[PVA0724]

Hi,

I have to recognize that every time I work with arrays in the code drive me crazy at some point...and this is not the exception...basically I modified a code I found to create an email and attach an specific set to worksheets...and it worked, now I took the same and added a part in order to retrieve only visible sheets but when I run the following code it only retrieved the first sheet ...and I need all the visible ones.

I have tried by entering the name of each one of the 18 -yeah I know, but I wanted to give it a try - and got the Subscript out of range error, also I tried with Sheets property with the same result and tried with the option xlSheetVisible but it didn't work.

I really appreciated if someone could point to what is wrong in the array. Thanks

Sub Email_Sheets()
   Dim oApp As Object
   Dim oMail As Object
   Dim LWorkbook As Workbook
   Dim LFileName As String
   Dim wks As Worksheet
   
   
   
   'Turn off screen updating
   Application.ScreenUpdating = False
   
   For Each wks In Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18))
   
    'If wks.Visible = xlSheetVisible Then
     If wks.Visible = True Then
        wks.Copy
        Exit For
    End If
    Next wks
      
   Set LWorkbook = ActiveWorkbook
   
   'Create a temporary file in your current directory that uses the name
   ' of the sheet as the filename
   LFileName = "Indemnizaciones Procesadas" & " " & Format(Now, "dd-mmm-yy@")
   On Error Resume Next
   'Delete the file if it already exists
   Kill LFileName
   On Error GoTo 0
   'Save temporary file
   LWorkbook.SaveAs Filename:=LFileName
   
   'Create an Outlook object and new mail message
   Set oApp = CreateObject("Outlook.Application")
   Set oMail = oApp.CreateItem(0)
   
   'Set mail attributes (uncomment lines to enter attributes)
   ' In this example, only the attachment is being added to the mail message
   With oMail
      '.To = "[EMAIL="user@yahoo.com"]user@yahoo.com[/EMAIL]"
      '.Subject = "Subject"
      '.body = "This is the body of the message." & vbCrLf & vbCrLf & _
      '"Attached is the file"
      .Attachments.Add LWorkbook.FullName
      .Display
   End With
   
   'Delete the temporary file and close temporary Workbook
   LWorkbook.ChangeFileAccess Mode:=xlReadOnly
   Kill LWorkbook.FullName
   LWorkbook.Close SaveChanges:=False
   
   'Turn back on screen updating
   Application.ScreenUpdating = True
   Set oMail = Nothing
   Set oApp = Nothing
   
End Sub

 

[Damon Ostrander]

Hi PVA0724,

It only retrieves the first sheet because the Exit For statement exits the loop when a visible sheet is found, only permitting one visible sheet to be retrieved. Just eliminate the Exit For.

Damon

 

[PVA0724]

Hi Damon

Thank you! It worked...I don't know why I didn't see that! Perhaps because it was 11 pm Again thank you!