For Each Loop using worksheet names

Dreamteam

New Member
Joined
Feb 22, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,188
your captions says loop thru worksheets, but you seem to be using ranges.

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

New Member
Joined
Feb 22, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
For Each rngstartcell In rngstartcellcolumn

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

Dreamteam

New Member
Joined
Feb 22, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,

Many, many thanks - this has worked perfectly.

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

dt
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
Adding the Cstr converts the number to a string. Therefore Worksheets is looking at the name rather than the sheet index
 

Dreamteam

New Member
Joined
Feb 22, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,170,969
Messages
5,873,052
Members
432,959
Latest member
Missymoo

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
Top