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

Thread: Create new worksheet for each vendor

  1. #11
    Board Regular
    Join Date
    Mar 2015
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create new worksheet for each vendor

    Quote Originally Posted by Fluff View Post
    Ok how about
    Code:
    Sub i8ur4re()
       Dim Cl As Range
       Dim Ws As Worksheet
       Dim Ky As Variant
       Dim UsdRws As Long
       
       Set Ws = ActiveSheet
       UsdRws = Ws.Range("C" & Rows.Count).End(xlUp).Row
       With CreateObject("scripting.dictionary")
          For Each Cl In Ws.Range("C2:C" & UsdRws)
             .Item(Cl.Value) = Empty
          Next Cl
          For Each Ky In .keys
             Ws.Range("A1:Z" & UsdRws).AutoFilter 3, Ky
             Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
             Ws.AutoFilter.Range.Copy Range("A1")
          Next Ky
          Ws.AutoFilterMode = False
       End With
    End Sub
    Its not importing the data to the sheets and its still giving me the same error with the characters, etc.

    Ws.Range("A1:Z" & UsdRws).AutoFilter 3, Ky

    Would you like me to send you a copy of the excel sheet to take a look at, maybe im missing something on my end.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,112
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Create new worksheet for each vendor

    You can upload the file to a share site such as OneDrive, DropBox, GoogleDrive. Then mark for sharing & post the link to the thread.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Mar 2015
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create new worksheet for each vendor

    Quote Originally Posted by Fluff View Post
    You can upload the file to a share site such as OneDrive, DropBox, GoogleDrive. Then mark for sharing & post the link to the thread.
    Thank you in advance for helping me out on this, here you go

    https://drive.google.com/file/d/1xPD...ew?usp=sharing

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,112
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Create new worksheet for each vendor

    That's asking me to sign-in. You need to mark it for sharing & then post the link it gives you.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    Board Regular
    Join Date
    Mar 2015
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create new worksheet for each vendor

    Quote Originally Posted by Fluff View Post
    That's asking me to sign-in. You need to mark it for sharing & then post the link it gives you.
    Here you go:

    https://drive.google.com/file/d/1xPD...ew?usp=sharing

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,112
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Create new worksheet for each vendor

    Thanks for the file, the problem is that your vendors are in col H, not col C. Try this instead.
    Code:
    Sub i8ur4re()
       Dim Cl As Range
       Dim Ws As Worksheet
       Dim Ky As Variant
       Dim UsdRws As Long
       
       Set Ws = ActiveSheet
       Application.ScreenUpdating = False
       UsdRws = Ws.Range("H" & Rows.Count).End(xlUp).Row
       With CreateObject("scripting.dictionary")
          For Each Cl In Ws.Range("H2:H" & UsdRws)
             .Item(Cl.Value) = Empty
          Next Cl
          For Each Ky In .keys
             Ws.Range("A1:AR1" & UsdRws).AutoFilter 8, Ky
             Sheets.Add(, Sheets(Sheets.Count)).Name = Left(Ky, 30)
             Ws.AutoFilter.Range.Copy Range("A1")
          Next Ky
          Ws.AutoFilterMode = False
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    Board Regular
    Join Date
    Mar 2015
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create new worksheet for each vendor

    Quote Originally Posted by Fluff View Post
    Thanks for the file, the problem is that your vendors are in col H, not col C. Try this instead.
    Code:
    Sub i8ur4re()
       Dim Cl As Range
       Dim Ws As Worksheet
       Dim Ky As Variant
       Dim UsdRws As Long
       
       Set Ws = ActiveSheet
       Application.ScreenUpdating = False
       UsdRws = Ws.Range("H" & Rows.Count).End(xlUp).Row
       With CreateObject("scripting.dictionary")
          For Each Cl In Ws.Range("H2:H" & UsdRws)
             .Item(Cl.Value) = Empty
          Next Cl
          For Each Ky In .keys
             Ws.Range("A1:AR1" & UsdRws).AutoFilter 8, Ky
             Sheets.Add(, Sheets(Sheets.Count)).Name = Left(Ky, 30)
             Ws.AutoFilter.Range.Copy Range("A1")
          Next Ky
          Ws.AutoFilterMode = False
       End With
    End Sub

    That was the most beautiful thing ive seen all day, thank you so much for your help, beyond appreciated.

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,112
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Create new worksheet for each vendor

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •