Dynamically Create Sheets and Copy Data

[racer25]

Hi All,

Regretably ( or embarassingly ) I am not even sure where to start with this challenge - normally I can edit a bit (lot) of code and get something working.

INPUT - My challenge is I have approximately 26 columns of data in a long list with headings and a field call Account Exec. some of these values are also numeric - a Client Income Report.

OUTPUT - What I am looking to do is take the aforementioned data 1. Create a New Sheet for Each Person in the List 2. Copy the headings into Row 1 from original sheet 3. Copy data only for that person to that sheet 4. In Row 3 put in a down total.

I have attached a sample to hopefully show better how this should look.

Happy to expand further if anyone has a follow-up.

Thanks in advance.

Rob

[Mumps1]

Try:

Please Login or Register  to view this content.

[racer25]

Mumps1 - Thanks so much and its exactly what I was looking for.

Next challenge is not to simply copy paste but figure out what its doing - learn a bit

Have a great weekend

[Mumps1]

You are very welcome.

[racer25]

Try:

Please Login or Register  to view this content.

If I may ask for some direction please - if I wanted to do some additional formatting of the new sheets whereabouts in the code should I be working.

I did try the red below in a few places without success. My logic was it had to be around the area where summation was taking place. I am OK with formatting cells etc. just the placement is the issue (I think).

Thanks again for your help and guidance.

For Each rName In rngUniques
Set desWs = Sheets(rName.Value)
Sheets(rName.Value).UsedRange.Offset(1, 0).ClearContents
Sheets("Sheet1").Range("E1:E" & bottomE).AutoFilter Field:=1, Criteria1:=rName
Sheets("Sheet1").Range("E2:E" & bottomE).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(rName.Value).Cells(Rows.Count, "A").End(xlUp).Offset(4, 0)
LastRow = Sheets(rName.Value).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each rng In Sheets(rName.Value).Range("G3:Y3")
rng = WorksheetFunction.Sum(desWs.Range(desWs.Cells(5, rng.Column), desWs.Cells(LastRow, rng.Column)))
rng.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""??_-;_-@_-"
Next rng
Sheets(rName.Value).Select Selection.EntireColumn.AutoFit
If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
Next rName

[mgs73]

Your code in red replaced by this should work:

Please Login or Register  to view this content.

Also, after setting desWs = Sheets(rName.Value), there is no need to keep typing Sheets(rName.Value) - just type desWs.

E.g. change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Mumps1]

Try this slightly modified version:

Please Login or Register  to view this content.

[racer25]

Try this slightly modified version:

Please Login or Register  to view this content.

Thanks again - works a treat.

[Mumps1]

My pleasure. By the way, when you respond, you don't have to respond with a quote.