Loop to create newsheets for each owner & copy, paste data of each owner into the new sheet

[suresh7860]

HI Experts

Good Morning!!

I am have created a macro to consolidate 3 different reports to combine score, criteria and owner details by using combo and convert the report into pdf which is working fine but now requirement has changed that i should report each owner data into different tab and convert it but i am not able to get the details like how to export all the rows of each owner into different tab and rename the tabs as per the owner as i am very poor with vba loops. If any of the experts can help me to export data of each owner for a particular sheet and rename it with owner name and loop should run until the lastrow.

We don't have list of owners and number of rows, here it should create 3 sheets as we have 3 owners (Suresh, Sainath & Sanjay) and entire rows related to each owner should be moved to respective sheet and rename the sheets as Suresh, Sanjay & Sainath. I tried to research in google for this but didnt find anything suitable one whichever i got was creating muliple sheets as owner name will be listed many times and which as per my criteria we should have only one sheet for each owner name with his details.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rows
[/TD]
[TD]Combo (A)
[/TD]
[TD]Owner (B)
[/TD]
[TD]Criteria(C)
[/TD]
[TD]Standard (D)
[/TD]
[TD]Achieved(E)
[/TD]
[TD]Total Score(F)
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]D[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]E[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]I[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]1001-100001-A001[/TD]
[TD]Suresh[/TD]
[TD]J[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]D[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]E[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]I[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]1002-100002-A002[/TD]
[TD]Sanjay[/TD]
[TD]J[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]D[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]E[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]I[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]1003-100003-A003[/TD]
[TD]Sainath[/TD]
[TD]J[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]D[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]E[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]36
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]37
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]38
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]39
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]I[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]1004-100004-A004[/TD]
[TD]Suresh[/TD]
[TD]J[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]41
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]42
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]43
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]44
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]D[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]45
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]E[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]46
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]47
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]48
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]49
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]I[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]50
[/TD]
[TD]1005-100005-A005[/TD]
[TD]Sanjay[/TD]
[TD]J[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]51
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]52
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]53
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]54
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]D[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]55
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]E[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]56
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]57
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]58
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]59
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]I[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]60
[/TD]
[TD]1006-100006-A006[/TD]
[TD]Suresh[/TD]
[TD]J[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]61
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]62
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]63
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]64
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]D[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]65
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]E[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]66
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]67
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]68
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]69
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]I[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]70
[/TD]
[TD]1007-100007-A007[/TD]
[TD]Sainath[/TD]
[TD]J[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for your time & help!

Regards
Suresh7860

 

[Ombir]

For Input Data in Sheet1, Try this:

Sub Suresh()
Dim i       As Long
Dim rng     As Range
Dim Q       As Variant
Dim dict    As Object
Dim ar      As Variant

Set rng = Sheets("Sheet1").Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
Set dict = CreateObject("scripting.dictionary")

With dict
For Each cell In rng
    If Not .exists(cell.Value) Then
        i = 1
        ReDim ar(1 To Application.CountIf(rng, cell.Value), 1 To 6)
        ar(i, 1) = cell.Offset(, -1): ar(i, 2) = cell.Value: ar(i, 3) = cell.Offset(, 1)
        ar(i, 4) = cell.Offset(, 2): ar(i, 5) = cell.Offset(, 3): ar(i, 6) = cell.Offset(, 4)
        .Add cell.Value, Array(ar, i)
    Else
        Q = .Item(cell.Value)
        Q(1) = Q(1) + 1
        Q(0)(Q(1), 1) = cell.Offset(, -1): Q(0)(Q(1), 2) = cell.Value: Q(0)(Q(1), 3) = cell.Offset(, 1)
        Q(0)(Q(1), 4) = cell.Offset(, 2): Q(0)(Q(1), 5) = cell.Offset(, 3): Q(0)(Q(1), 6) = cell.Offset(, 4)
        .Item(cell.Value) = Q
    End If
Next

For Each k In .keys
    Worksheets.Add().Name = k
    Sheets(k).Range("A1:F1") = Array("Combo(A)", "Owner(B)", "Criteria (C)", "Standard (D)", "Achieved (E)", "Total Score (F)")
    Sheets(k).Range("A2").Resize(.Item(k)(1), 6) = .Item(k)(0)
    Sheets(k).Columns.AutoFit
Next

End With
End Sub

Regards,
Ombir

 

[suresh7860]

Thank you Ombir its working fine Have a great weekend!!