Scenario:

I have a workbook with names of people, managers and telephone costs. I
need to email the managers a list of all of their people and their telephone
costs. So, I begin by making a Pivot Table of my data (grouped by manager)
and then when I double-click on the total costs, it automatically creates a
new sheet detailing all of the staff and their costs. Lovely! Now I want to
send these individual sheets to the managers (i.e. the one sheet only, not
the rest of the workbook). Can I do this? The manager's email is in one of
the fields, if that helps.

Anyway, what I tried to do was this. Record a macro, double-click on the
pivot table cell, a new sheet is created. Copy the cell with the managers
name on and paste into the sheet name. Move the sheet to a new workbook.
Save the workbook and paste the sheet name into the saved name. Do File,
Send as Attachment, paste name in To box and Send. Carry on until all
managers are done.

However, I come stuck right near the beginning because my VBA is saying
select sheet 3, when of course next time it will be sheet 4 and therefore is
still looking for sheet 3. Can I say somehow, just select the active sheet
that I am on?

I can attach excel spreadsheet if you like?

Many thanks is much appreciated.