For Each Loop using worksheet names

[Dreamteam]

Hi guys,

I am having an issue with the below code. I am guessing that this is rookie mistake but that said I have been trying for a day now with no avail. I am simply trying to use gmail to send various employees worksheets based on their employee code (eg, 1,3,4) and the data is in worksheets named the same way. Sending the mail is no problem but the code is reading the worksheets as an index and therefore sending the wrong ones. Any help would be much appreciated as it is driving me mad... A sample is below...



For Each rngstartcell In rngstartcellcolumn

Set rng = Worksheets(rngstartcell).Range("a1")
Set rng = rng.CurrentRegion
driveremail = rngstartcell.Offset(0, -2)
drivertotal = rngstartcell.Offset(0, 1)


With Application
.EnableEvents = False
.ScreenUpdating = False
End With




With iMsg
Set .Configuration = iConf
.to = driveremail
.CC = ""
.BCC = ""
.From = """*****"" <*******@gmail.com>"
.Subject = "May Acc Work" & drivertotal
.HTMLBody = RangetoHTML(rng)
.Send
End With


Next rngstartcell

 

[ranman256]

your captions says loop thru worksheets, but you seem to be using ranges.

dim sht as worksheet

for each sht in worksheets
    sht.activate

    msgbox sht.name
next

once you connect to the sheet, THEN send the range of data.

 

[Dreamteam]

Hi ranman, thanks for replying. The loop is a range of cells on a summary page that holds employee codes eg, 1,3,8 etc. I have corresponding worksheets names 1,3,8 etc. I just want to loop through the list and send the corresponding worksheet by email.

I may have approached this task completely wrong by the way...

 

[Fluff]

How about

For Each rngstartcell In rngstartcellcolumn

Set rng = Worksheets(cstr(rngstartcell)).Range("a1").CurrentRegion
driveremail = rngstartcell.Offset(0, -2)
drivertotal = rngstartcell.Offset(0, 1)

 

[Dreamteam]

Hi Fluff,

Many, many thanks - this has worked perfectly.

Can I please ask about this syntax if you have a moment?

dt

 

[Fluff]

Adding the Cstr converts the number to a string. Therefore Worksheets is looking at the name rather than the sheet index

 

[Dreamteam]

Oh, I am so pleased with this Fluff. I was thinking that my only option would be to manually move the sheets in order; but practically that really would never work.

I have now tried rearranging the order of the sheets and your suggestion works perfectly.

Once again - thank you :~)

 

[Fluff]

Glad we could help & thanks for the feedback