Create a separate worksheet for every unique name in Column A and copy data to each worksheet
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    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
    Regards Dante Amor

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,575
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

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

    or instead of many sheets just one with the choice of an engineer and data change according to the choice

    Engineer Engineer Number Queue Record Type Subject Status~ Severity Age in Days
    Bashful Bashful
    3028
    TAC Incident - Vz oRAN 73.894 Time Warner Hub BBU1 alpha not taking traffic REJECTED Severity 4
    0
    Bashful
    3073
    TAC Incident - Vz oRAN VLSM does not launch RESOLVED Severity 4
    102
    Bashful
    3105
    TAC Incident - Vz oRAN not processing traffic RECOVERED Severity 3
    100
    Bashful
    3486
    TAC Incident - Vz oRAN 73235 Bushnell Basin Cell unavailable with context drop PENDING RELEASE Severity 3
    78
    Bashful
    3495
    TAC Incident - Vz oRAN Can not take 911 / not visible vSLM Winder Medium RESOLVED Severity 4
    77
    Bashful
    3565
    TAC Incident - Vz oRAN 70207 and 70308 RRHs with OPA RECOVERED Severity 4
    71
    Last edited by sandy666; Jul 19th, 2019 at 06:51 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  5. #5
    New Member
    Join Date
    May 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Quote Originally Posted by DanteAmor View Post
    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

  6. #6
    New Member
    Join Date
    May 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Quote Originally Posted by alansidman View Post
    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.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by jconkl02 View Post
    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.
    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
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by jconkl02 View Post
    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
    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 by Steve_; Jul 22nd, 2019 at 11:52 AM.

  9. #9
    New Member
    Join Date
    May 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Steve_
    I'm getting a compile error on the 3rd line:
    ReDim shArray(Sheets"Tickets").UsedRange.Rows.Count, Sheets("Tickets").UsedRange.Columns.Count)

  10. #10
    New Member
    Join Date
    May 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •