Complicated Project
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Complicated Project

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complicated Project

    I'm new to VBA and so far i've mostly copied other macros and adjusted them to my needs. So i don't fully understand the language.

    I deal with A LOT of data at work, usually a table with thousands of rows of data, and about a dozen columns. I always have to filter the data on a specific item code, then copy and paste that into a new worksheet as sort of a "snapshot" of the data that corresponds to that item code.

    Is there a way with VBA to create a tool to where i could put the entire table of data onto a "control" sheet and then have a macro individually run a filter on each item code, create a "snapshot" (copy and paste the filtered table as values) to a newly created sheet, maybe even with that item code as the new sheet name?

    Examples of item codes are AA, BB, CC, DD, and roughly 20 others.
    Examples of data that corresponds to these item codes are labor hours, charge numbers, activity IDs, etc.

    I know this is really complicated, but it would save me literally hundreds of hours. Any help at all is greatly appreciated! And if something like this has already been answered please point me in the direction and i will happily figure it out. Everything i've found so far though i have not been skilled enough to change it to meet this specific need.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,379
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Complicated Project

    You could improve your chance of getting some help if you post some sample data and a sample of the results you would like to see. See this link for how to post your data. https://www.mrexcel.com/forum/about-...tachments.html
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Kuwait
    Posts
    449
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Project

    Welcome to Mr. Excel

    As JoeMo said, with so little details it would mean that we have to make some assumption that may not be correct. Try the below VBA code, I am assuming your data starts in row 1 … Let us know how it goes

    Code:
    Sub SheetPerItem()
    Application.ScreenUpdating = False
    Dim ArList As Object, Ar As Variant, Col As Long, Ws As Worksheet
    Dim ColName As String, lRow As Long, Rg As Range
    Set ArList = CreateObject("System.Collections.ArrayList")
    Set Ws = ActiveSheet
    Col = Application.InputBox("Please click the column where the item code is located", Type:=8).Column
    Ar = ActiveSheet.Range("A1").CurrentRegion
    ColName = Ws.Cells(1, Col)
    lRow = UBound(Ar)
    For x = 2 To UBound(Ar)
        If Not ArList.contains(Ar(x, Col)) Then ArList.Add Ar(x, Col)
    Next
    ReDim Ar(1 To ArList.Count): Ar = ArList.ToArray
    For x = 0 To UBound(Ar)
        Sheets.Add(after:=Sheets(Sheets.Count)).Name = Ar(x)
        Set Rg = Ws.Cells(lRow + 2, Col).Resize(2)
        Rg = Application.Transpose(Array(ColName, Ar(x)))
        Ws.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, Rg, Sheets(Ar(x)).[A1]
    Next
    Rg.Delete
    Application.ScreenUpdating = True
    End Sub

    Check the List of BB codes


  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Project

    Quote Originally Posted by mse330 View Post
    Welcome to Mr. Excel

    As JoeMo said, with so little details it would mean that we have to make some assumption that may not be correct. Try the below VBA code, I am assuming your data starts in row 1 … Let us know how it goes

    Code:
    Sub SheetPerItem()
    Application.ScreenUpdating = False
    Dim ArList As Object, Ar As Variant, Col As Long, Ws As Worksheet
    Dim ColName As String, lRow As Long, Rg As Range
    Set ArList = CreateObject("System.Collections.ArrayList")
    Set Ws = ActiveSheet
    Col = Application.InputBox("Please click the column where the item code is located", Type:=8).Column
    Ar = ActiveSheet.Range("A1").CurrentRegion
    ColName = Ws.Cells(1, Col)
    lRow = UBound(Ar)
    For x = 2 To UBound(Ar)
        If Not ArList.contains(Ar(x, Col)) Then ArList.Add Ar(x, Col)
    Next
    ReDim Ar(1 To ArList.Count): Ar = ArList.ToArray
    For x = 0 To UBound(Ar)
        Sheets.Add(after:=Sheets(Sheets.Count)).Name = Ar(x)
        Set Rg = Ws.Cells(lRow + 2, Col).Resize(2)
        Rg = Application.Transpose(Array(ColName, Ar(x)))
        Ws.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, Rg, Sheets(Ar(x)).[A1]
    Next
    Rg.Delete
    Application.ScreenUpdating = True
    End Sub
    I tried it twice with fresh data and it worked fantastic the first time but then i received this error the second time. Any ideas?


  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Complicated Project

    What was the error message & number & what line of code was highlighted when you click Debug?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Project

    The screenshot did not post but it was an error on this line For x = 2 To UBound(Ar)

    with the "x" highlighted and a message box that said "Compile error: expected function or variable"

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Complicated Project

    Not quite sure why you would have got that error, if it worked first time round.
    Try deleting the code & then copy paste it back in. Do you still get the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Oct 2007
    Location
    Kuwait
    Posts
    449
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Project

    Why do you need to run the code a 2nd time ? If you do, you should get an error that the same sheet name exists but I'm not sure why you get the error message at that line. Were you in the same sheet that you run the code the 2nd time? Because I'm using ActiveSheet

    Check the List of BB codes


  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Project

    Quote Originally Posted by Fluff View Post
    Not quite sure why you would have got that error, if it worked first time round.
    Try deleting the code & then copy paste it back in. Do you still get the error?
    I deleted it and copy/pasted it in and got the same error. The line that it is actually highlighting in yellow and pointing to is the very first line "Sub SheetPerItem()" but then highlights the "x" on the "For x = 2 To UBound(Ar)" line as well.

    The weird thing is it works fine with a much smaller sample data set on my personal laptop, but then when i copied the macro over to my work laptop to use it with the real data set, this is when i got the error.

  10. #10
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Project

    Alright, i created a whole new module and typed it out myself and it works now, mostly.

    It does the job but then at the very end it creates an "extra" sheet and then hits an error on this line:

    Sheets.Add(after:=Sheets(Sheets.Count)).Name = Ar(x)

    It is the more typical error of "Run-time error 1004. Application-defined or object-defined error"
    This honestly isnt a huge deal as i can just delete the extra sheet but it would be great if it's an easy fix

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
  •