Array to pull out only visible worksheets

PVA0724

New Member
Joined
Apr 21, 2012
Messages
48
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

Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top