Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: VBA solution to create worksheets based on cell value and then copy data to its new worksheet

  1. #11
    Board Regular
    Join Date
    May 2009
    Location
    Fort Worth, TX
    Posts
    444
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA solution to create worksheets based on cell value and then copy data to its new worksheet

    The shipper column is column J.

    You can see that there are two transactions for shipper (OR CUSTOMER) ADVANCSTEREC, so I would want all of their transactions in the same work sheet. The rows end in column Q (I left off a few columns, below)


    Init Number Station ST Track/
    Sequence
    Train ID Division Origin
    Station
    Or
    ST
    Shipper Destination
    Station
    Ds
    ST
    Consignee
    ADMX 15599 BARSTOW CA 1508- 34 CALIFORNIA MARSHALL MN ADMCORPROCES COLTON CA ARCHERDANMID
    BNSF 518052 KAISER CA 2126- 1 CALIFORNIA FONTANA CA ADVANCSTEREC ARMOREL AR NUCORYSTEEL
    MWCX 204154 KAISER CA 2126- 2 CALIFORNIA FONTANA CA ADVANCSTEREC BEAUMONT TX OPTIMUSTELLC
    GRW 4391 PITTSBURG CA 0699-107 CALIFORNIA STLOUIS MO AGENT MADISON IL DELIVESWITCH
    GACX 55610 SPRINGFIE MO 0111- 18 HEARTLAND KANCITY KS AGPROINCACOO MONETT MO TYSONFOODS
    TILX 54864 AMASYARD TX 0104- 11 KANSAS DAWSON MN AGPROINCACOO DALHART TX CARGILL
    TILX 55193 AMASYARD TX 0104- 12 KANSAS DAWSON MN AGPROINCACOO DALHART TX CARGILL
    -Steve

    Committed to learning VBA!
    MS Excel 2007 - Windows XP
    MS Excel 2004 - Mac OS X

  2. #12
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,937
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA solution to create worksheets based on cell value and then copy data to its new worksheet

    Considering the following:
    - Name Sheet: "test".
    - headings: row 3.
    - start of data row 4.
    - Important: customer sheets do not exist.

    Try:

    Code:
    Sub create_worksheets()
      Dim c As Range, sh As Worksheet, ky As Variant
      Set sh = Sheets("test")
      With CreateObject("scripting.dictionary")
         For Each c In sh.Range("J4", sh.Range("J" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
         Next c
         For Each ky In .Keys
            sh.Range("A3").AutoFilter Columns("J").Column, ky
            Sheets.Add(, Sheets(Sheets.Count)).Name = ky
            sh.AutoFilter.Range.EntireRow.Copy Range("A3")
         Next ky
      End With
      sh.Select
      sh.ShowAllData
    End Sub
    Regards Dante Amor

  3. #13
    Board Regular
    Join Date
    May 2009
    Location
    Fort Worth, TX
    Posts
    444
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA solution to create worksheets based on cell value and then copy data to its new worksheet

    Looks like this is going to work. Really appreciate your assistance!!!
    -Steve

    Committed to learning VBA!
    MS Excel 2007 - Windows XP
    MS Excel 2004 - Mac OS X

  4. #14
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,937
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA solution to create worksheets based on cell value and then copy data to its new worksheet

    Ok, try and tell me.
    Regards Dante Amor

  5. #15
    Board Regular
    Join Date
    May 2009
    Location
    Fort Worth, TX
    Posts
    444
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA solution to create worksheets based on cell value and then copy data to its new worksheet

    Quote Originally Posted by DanteAmor View Post
    Ok, try and tell me.
    It works! Thank you very much!!!
    -Steve

    Committed to learning VBA!
    MS Excel 2007 - Windows XP
    MS Excel 2004 - Mac OS X

  6. #16
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,937
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA solution to create worksheets based on cell value and then copy data to its new worksheet

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

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
  •