Create a separate worksheet for every unique name in Column A and copy data to each worksheet

J

jconkl02

Board Regular
Joined
May 25, 2016
Messages
55
I need some help. I want to create separate worksheets for each unique name found in Column A of the first worksheet in the workbook. Then I want to copy the entire row of data in the first worksheet to the newly created worksheets.


Using "Doc" as an example. A new worksheet is created named "Doc" and then each row of data that has "Doc" in column A would get copied to the new worksheet named "Doc". I need that for all the names in Column A. There will be some entries that just have a hyphen in Column A. It too needs it's own worksheet. My work laptop isn't allow me to install the MrExcel HTML Maker, so I'm just cutting and pasting it. I know it's not preferred.

The top row is a header starting at A1

[TABLE="width: 1158"]
<tbody>[TR]
[TD]Engineer[/TD]
[TD]Number[/TD]
[TD]Queue[/TD]
[TD]Record Type[/TD]
[TD]Subject[/TD]
[TD]Status~[/TD]
[TD]Severity[/TD]
[TD]Age in Days[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD="align: right"]2292[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Can not update firmware on RRH[/TD]
[TD]REJECTED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Cornelius[/TD]
[TD="align: right"]2996[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]VSWR Alarm at Eastham 2 C57277.[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD]Bashful[/TD]
[TD="align: right"]3028[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]73.894 Time Warner Hub BBU1 alpha not taking traffic[/TD]
[TD]REJECTED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Dopey[/TD]
[TD="align: right"]3039[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]062041 site RRH[0-5-0] configuration no longer exist[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD]Bashful[/TD]
[TD="align: right"]3073[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]VLSM does not launch[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD]Doc[/TD]
[TD="align: right"]3079[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]ALU RRH CELL 22 NO TRAFFIC[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD]Bashful[/TD]
[TD="align: right"]3105[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]not processing traffic[/TD]
[TD]RECOVERED[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Snow White[/TD]
[TD="align: right"]3106[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Test VSWR on Nokia 850 RRHs[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Cornelius[/TD]
[TD="align: right"]3138[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]RET issue on Converted sites[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD="align: right"]3201[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]73.209 Latta Road AWS RRH overpower alarms[/TD]
[TD]PENDING CUSTOMER[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD]Huckepack[/TD]
[TD="align: right"]3348[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Site not 100% usable in the vLSM[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]86[/TD]
[/TR]
[TR]
[TD]Doc[/TD]
[TD="align: right"]3433[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]066167 Alpha AWS Down[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]82[/TD]
[/TR]
[TR]
[TD]Sneezy[/TD]
[TD="align: right"]3457[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]ALPT old data is included in new tar files[/TD]
[TD]PENDING RCA[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Bashful[/TD]
[TD="align: right"]3486[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]73235 Bushnell Basin Cell unavailable with context drop[/TD]
[TD]PENDING RELEASE[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]Bashful[/TD]
[TD="align: right"]3495[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Can not take 911 / not visible vSLM Winder Medium[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]Purzelbaum[/TD]
[TD="align: right"]3500[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]CPRI oos[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]Sleepy[/TD]
[TD="align: right"]3513[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]RRH OVER POWER[/TD]
[TD]REJECTED[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD="align: right"]3528[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]All 850 sectors alarming TX-OUT-OF-ORDER[/TD]
[TD]PENDING CUSTOMER[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]Sneezy[/TD]
[TD="align: right"]3562[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Can't access LSM GUI from SANE[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Bashful[/TD]
[TD="align: right"]3565[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]70207 and 70308 RRHs with OPA[/TD]
[TD]RECOVERED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD="align: right"]3572[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]070308_SCRANTON[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Pick[/TD]
[TD="align: right"]3574[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Unstitched CSL spikes on DCM blades[/TD]
[TD]PENDING CUSTOMER[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Sleepy[/TD]
[TD="align: right"]3575[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]vLSM inquiry[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Purzelbaum[/TD]
[TD="align: right"]3635[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]cells-oos[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Sleepy[/TD]
[TD="align: right"]3637[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Yorkshire Cell 070212[/TD]
[TD]RESOLVED[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Sleepy[/TD]
[TD="align: right"]3658[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]074080 High RSSI alarm when Power enabled on ALD Ports[/TD]
[TD]ASSIGNED[/TD]
[TD]Severity 3[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]Sneezy[/TD]
[TD="align: right"]3660[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Multiple sites having RRH DC Input Fail - 42 RRHs[/TD]
[TD]ASSIGNED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]Sneezy[/TD]
[TD="align: right"]3661[/TD]
[TD]TAC[/TD]
[TD]Incident - Vz oRAN[/TD]
[TD]Multiple Sites having RRH Over Power Alarm -124 RRHs[/TD]
[TD]ASSIGNED[/TD]
[TD]Severity 4[/TD]
[TD="align: right"]64[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Try this

Code: 
Option Explicit
Sub Create_separate_worksheet()
    Dim c As Range, sh As Worksheet, Ky As Variant
    
    Set sh = Sheets(1)
    With CreateObject("scripting.dictionary")
        For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
        Next c
        For Each Ky In .Keys
            sh.Range("A1").AutoFilter 1, Ky
            Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
            sh.AutoFilter.Range.EntireRow.Copy Range("A1")
        Next Ky
    End With
    sh.ShowAllData
End Sub
 
Upvote 0
or instead of many sheets just one with the choice of an engineer and data change according to the choice

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Engineer[/td][td][/td][td=bgcolor:#70AD47]Engineer[/td][td=bgcolor:#70AD47]Number[/td][td=bgcolor:#70AD47]Queue[/td][td=bgcolor:#70AD47]Record Type[/td][td=bgcolor:#70AD47]Subject[/td][td=bgcolor:#70AD47]Status~[/td][td=bgcolor:#70AD47]Severity[/td][td=bgcolor:#70AD47]Age in Days[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Bashful[/td][td][/td][td=bgcolor:#E2EFDA]Bashful[/td][td=bgcolor:#E2EFDA]
3028​
[/td][td=bgcolor:#E2EFDA]TAC[/td][td=bgcolor:#E2EFDA]Incident - Vz oRAN[/td][td=bgcolor:#E2EFDA]73.894 Time Warner Hub BBU1 alpha not taking traffic[/td][td=bgcolor:#E2EFDA]REJECTED[/td][td=bgcolor:#E2EFDA]Severity 4[/td][td=bgcolor:#E2EFDA]
0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Bashful[/td][td]
3073​
[/td][td]TAC[/td][td]Incident - Vz oRAN[/td][td]VLSM does not launch[/td][td]RESOLVED[/td][td]Severity 4[/td][td]
102​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Bashful[/td][td=bgcolor:#E2EFDA]
3105​
[/td][td=bgcolor:#E2EFDA]TAC[/td][td=bgcolor:#E2EFDA]Incident - Vz oRAN[/td][td=bgcolor:#E2EFDA]not processing traffic[/td][td=bgcolor:#E2EFDA]RECOVERED[/td][td=bgcolor:#E2EFDA]Severity 3[/td][td=bgcolor:#E2EFDA]
100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Bashful[/td][td]
3486​
[/td][td]TAC[/td][td]Incident - Vz oRAN[/td][td]73235 Bushnell Basin Cell unavailable with context drop[/td][td]PENDING RELEASE[/td][td]Severity 3[/td][td]
78​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Bashful[/td][td=bgcolor:#E2EFDA]
3495​
[/td][td=bgcolor:#E2EFDA]TAC[/td][td=bgcolor:#E2EFDA]Incident - Vz oRAN[/td][td=bgcolor:#E2EFDA]Can not take 911 / not visible vSLM Winder Medium[/td][td=bgcolor:#E2EFDA]RESOLVED[/td][td=bgcolor:#E2EFDA]Severity 4[/td][td=bgcolor:#E2EFDA]
77​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Bashful[/td][td]
3565​
[/td][td]TAC[/td][td]Incident - Vz oRAN[/td][td]70207 and 70308 RRHs with OPA[/td][td]RECOVERED[/td][td]Severity 4[/td][td]
71​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
Why not just filter on each Engineer's name to see only that Data. Highlight your top line which displays your categories. Click on Data. Click on Filter. In the Engineer Column, click on the dropdown arrow and select an Engineer.
 
Upvote 0
Thank you DanteAmor. You got me half way to my goal. Your code generates separate worksheets for each engineer and puts a header row at the top of each, but it does not copy the rows associated with each engineer to the appropriate worksheet.

DanteAmor said:
Try this

Code: 
Option Explicit
Sub Create_separate_worksheet()
    Dim c As Range, sh As Worksheet, Ky As Variant
    
    Set sh = Sheets(1)
    With CreateObject("scripting.dictionary")
        For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
        Next c
        For Each Ky In .Keys
            sh.Range("A1").AutoFilter 1, Ky
            Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
            sh.AutoFilter.Range.EntireRow.Copy Range("A1")
        Next Ky
    End With
    sh.ShowAllData
End Sub
Click to expand...
 
Upvote 0
alansidman,

This is going to be a report that will go out to a list of engineers with ticket assignments for each to complete. My peer has been doing it manually for about a year and did not know that it could be automated. I know that it can, just not how it is done.

alansidman said:
Why not just filter on each Engineer's name to see only that Data. Highlight your top line which displays your categories. Click on Data. Click on Filter. In the Engineer Column, click on the dropdown arrow and select an Engineer.
Click to expand...
 
Upvote 0
jconkl02 said:
Thank you DanteAmor. You got me half way to my goal. Your code generates separate worksheets for each engineer and puts a header row at the top of each, but it does not copy the rows associated with each engineer to the appropriate worksheet.
Click to expand...

The macro works for me. Maybe there are spaces in column A, or something weird is in your data.
Try this:


Code: 
Sub Create_separate_worksheet()
    Dim c As Range, sh As Worksheet, Ky As Variant
    
    Set sh = Sheets(1)
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    With CreateObject("scripting.dictionary")
        For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
        Next c
        For Each Ky In .Keys
            sh.Range("A1:H" & sh.Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 1, Ky
            Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
            sh.AutoFilter.Range.EntireRow.Copy Range("A1")
        Next Ky
    End With
    sh.ShowAllData
End Sub
 
Upvote 0
Solution
jconkl02 said:
I need some help. I want to create separate worksheets for each unique name found in Column A of the first worksheet in the workbook. Then I want to copy the entire row of data in the first worksheet to the newly created worksheets.


Using "Doc" as an example. A new worksheet is created named "Doc" and then each row of data that has "Doc" in column A would get copied to the new worksheet named "Doc". I need that for all the names in Column A. There will be some entries that just have a hyphen in Column A. It too needs it's own worksheet. My work laptop isn't allow me to install the MrExcel HTML Maker, so I'm just cutting and pasting it. I know it's not preferred.

The top row is a header starting at A1
Click to expand...

See if this works for you.

Code: 
Sub Seperate_By_Engineer()
    Dim shArray()
    Dim ws As Worksheet
    ReDim shArray(Sheets"Tickets").UsedRange.Rows.Count, Sheets("Tickets").UsedRange.Columns.Count)
    shArray = Range(Cells(1, 1), Cells(UBound(shArray, 1), UBound(shArray, 2)))
    For i = LBound(shArray, 1) + 1 To UBound(shArray, 1)
        doCreate = True
        For Each sh In ThisWorkbook.Sheets
            If sh.Name = shArray(i, 1) Then
                doCreate = False
                Exit For
            End If
        Next sh
        If doCreate Then
            Set ws = ThisWorkbook.Sheets.Add
            ws.Name = shArray(i, 1)
            For x = LBound(shArray, 1) To UBound(shArray, 1)
                For y = LBound(shArray, 2) To UBound(shArray, 2)
                    If x = LBound(shArray, 1) Then
                        ws.Cells(x, y) = shArray(x, y)
                    Else
                        If shArray(x, 1) = shArray(i, 1) Then
                            If y = 1 Then curRow = ws.UsedRange.Rows.Count + 1
                            ws.Cells(curRow, y) = shArray(x, y)
                        End If
                    End If
                Next y
            Next x
        End If
    Next i
End Sub
 
Last edited:
Upvote 0
Steve_
I'm getting a compile error on the 3rd line:
ReDim shArray(Sheets"Tickets").UsedRange.Rows.Count, Sheets("Tickets").UsedRange.Columns.Count)
 
Upvote 0
DanteAmor,
Disregard my earlier reply where I said that it only made the worksheets, but didn't copy the data. I made a mistake. Your macro works like a champ. Thank you.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top