Excel 2010; copy per subtotal to new sheet

Have a look at the Advanced Filter Function.

This can filter to another location (within the same workbook) which can then be copied/exported out for sending to your users.

Thanks
Rob H

Thanks Rob. Yes that would work, only it still remains a manual thing to do (create a filter per user). Since in this case the user count is almost 300, I'd rather have this in a macro.
Cheers.

It could be automated to run through a list of users and change the filter for each and create the sheet for each.

Thanks
Rob H

Do you know how by any chance?

Yes, but I would need a sample file to work on.

I assume each user will have multiple entries in data list so will also need a list of users with single entry per user. For purposes of upload, this can be fictitious so as not to divulge sensitive info eg User1, User2, User3 etc.

For the data list, I would need the column headings and sample sample rows, 20+ should suffice but again fictitious data.

Thanks
Rob

I hope the attached file is good enough.
So for all "jobs" that belong to the same "on call job-manager" a seperate worksheet needs to be created. Please try to have the entire row selected/exported (not all columns are in the sample file).
Many thanks and good luck.
W.

sample.xlsx

Just had another read of your original question, do you need each sheet to be copied to a separate file so that you can e-mail just their file?

Can incorporate a few extra lines to do that if so required.

Thanks
Rob H

In case you do need the above, in the script add these few lines:

1) At the very beginning of the script add one line:

SaveDir = Application.ActiveWorkbook.Path & "\Saved\"

Select allOpen in new window

This determines where to save the copied sheets, the current directory plus a sub-directory called "Saved"; the sub-directory will have to be created manually.

2) After "GoSub CreateFilter", line 29 of above snippet, add one line:

GoSub CreateWorkbook

Select allOpen in new window

3) After the CreateFilter SubRoutine, between lines 53 and 55 add the following snippet:

CreateWorkbook:
    Sheets(NewName).Copy
    SaveDate = Format(Date, "yyyymmdd")
    SaveName = NewName & "_" & SaveDate
    FullFileName = SaveDir & SaveName
    ActiveWorkbook.SaveAs _
        Filename:=FullFileName, _
        FileFormat:=xlOpenXMLWorkbook, _
        CreateBackup:=False
    ActiveWorkbook.Close
Return

Select allOpen in new window

This saves the copied sheet with the naming convention "ManagerName _ yyyymmdd.xlsx"

I have used the date format yyyymmdd so that when they are sorted by Filename in Windows Explorer, they appear in chronological order.

Thanks
Rob H

That looks real good Rob.
Allow me some time to check into it.
You will hear from me tomorrow.
And yes different workbook is a great idea.

Have a very nice day (you made mine).
W

Thanks. Much appreciated.