Solved

Excel 2010; copy per subtotal to new sheet

Posted on 2013-12-09
11
661 Views
Last Modified: 2013-12-10
Hi Experts,

I have data to be subtotalled 'per user',
Since I have to email the data to each of those users I wondered if there was a way to copy the rows  that belong to a particular user to a seperate sheet.
Attached a custom file.
Thanks a lot.
W.

export-per-change.xlsx
0
Comment
Question by:Watnog
  • 6
  • 5
11 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39705662
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
0
 

Author Comment

by:Watnog
ID: 39705757
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39705778
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
0
 

Author Comment

by:Watnog
ID: 39705870
Do you know how by any chance?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39705900
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Watnog
ID: 39706013
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
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39706271
Try the attached.

In addition to original data, I have added:

Range Names
On Data tab:
JOB_DATA  Refers to all data on Data tab. Range is dynamic using following formula:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))
This creates a range starting at Data!$A$1 and counts the entries in column A and row 1 to determine height and width of range. Assumes continuous data in column A and row 1; I added a # to row 1 of your coloured column just so it gets included.

Summary tab:
MANAGER_LIST  This refers to the list on Summary tab. This list is actually a simple Pivot Table using JOB_DATA as its source data. Again Dynamic, using:
=OFFSET(Summary!$A$1,1,0,COUNTA(Summary!$A:$A)-1,1)
The count of column is reduced by 1 to allow for Row Header.

On Info tab:
MGR_COUNT  Counts number of Managers from MANAGER_LIST

MGR_NUMBER  Number of Current Manager in Filter Routine

MGR_NAME  Name of Current Manager in Filter Routine.

For those that like to see the routine without downloading the whole file, it copied below.

Sub Filter()

Application.DisplayAlerts = False

'Clear Old Sheets
For Each Worksheet In ActiveWorkbook.Worksheets
ShtName = Worksheet.Name

Select Case ShtName
    Case "Info", "Data", "Summary"
'Ignore
    Case Else
    GoSub ClearSheet
End Select

Next Worksheet

'Reset List
ActiveWorkbook.RefreshAll

'Create New
For Mgr = 1 To Range("MGR_COUNT")
    Range("MGR_NUMBER") = Mgr
    Sheets.Add After:=Sheets(Sheets.Count)
    OldName = ActiveCell.Worksheet.Name
    NewName = Range("Mgr_Name")
    Sheets(OldName).Name = NewName

GoSub CreateFilter
Next Mgr

Application.DisplayAlerts = True
Exit Sub

ClearSheet:
    Sheets(ShtName).Select
    ActiveWindow.SelectedSheets.Delete
Return

CreateFilter:
    Range("JOB_DATA").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Crit1"), _
        CopyToRange:=Range("L2"), _
        Unique:=False
    Range("L2").Select
    Selection.CurrentRegion.Select
    Selection.Cut
    Sheets(NewName).Select
    Range("A1").Select
    ActiveSheet.Paste
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit
Return

End Sub

Open in new window

Routine looks at all sheets in the Workbook, if it is one of the required sheets (Info, Data or Summary) it ignores it, otherwise it deletes it; clearing out the old filtered data.

It then refreshes the Pivot on Summary tab to refresh the list of Managers.

It then inserts a sheet for each Manager in the list and creates a filter on the data, copying the filtered data to cell L2 on the data tab. This is then Cut and Pasted to the sheet for that Manager.

NB For additional columns in your Data sheet, adjust the reference to cell L2 to a column that is sufficiently far away from your proper data to allow for some blank columns between your data and the extract destination. The Cut and Paste uses the CurrentRegion Selection to select the data, if there is no gap between the source data and the extract data it would select everything, source and extract. Use row 2 so that it does not impact the count in the JOB_DATA dynamic range.

I think that covers everything, any questions feel free to post as you come across them.

Thanks
Rob H
sample.xlsm
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39706276
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39706332
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\"

Open 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

Open 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

Open 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
0
 

Author Comment

by:Watnog
ID: 39706345
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
0
 

Author Closing Comment

by:Watnog
ID: 39708313
Thanks. Much appreciated.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now