Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Excel VBA - Autofilter, copy, paste to new sheet

This is a discussion on Excel VBA - Autofilter, copy, paste to new sheet within the Excel Questions forums, part of the Question Forums category; Hello, Can anyone help me with some code that can autofilter, copy, and paste to a new sheet that is ...

  1. #1
    Board Regular
    Join Date
    Jan 2010
    Posts
    125

    Default Excel VBA - Autofilter, copy, paste to new sheet

    Hello,

    Can anyone help me with some code that can autofilter, copy, and paste to a new sheet that is named after the filtered field?

    Row 1 has column headers and column A contains the unique values that I want to filter by. There will be multiple rows containing each unique value.

    I don't know all of the unique values that could be in column A. Is there a way to write the code to make the macro filter each unique value without knowing what those values are?

    I also need it to create a new sheet and name the sheet after the unique value from column A.

    Next, it would need to copy the filtered data and paste it to the newly created sheet.

    It would need to repeat this process until each unique data set has been copied to its own sheet.

    Thanks for any help.

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,125

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    You can record a macro for the first filtered value, then post what you get back here for someone to set up dynamically for you.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    Board Regular
    Join Date
    Jan 2010
    Posts
    125

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    Here is the code that I recorded and edited.

    I don't know all of the different values that could be used in Criteria 1.
    The new sheet needs to be named after the value for Criteria 1.

    I hope this helps. Let me know if you have any more questions.

    Thanks

    HTML Code:
        Selection.AutoFilter
       
        ActiveSheet.Range("$A$1:$AJ$81142").AutoFilter Field:=5, Criteria1:= _
            "101 Transport Inc" 'This is the unique value that I don't know.
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "101 Transport Inc" 'The new sheet needs to be named after
        ActiveSheet.Paste                                                  ' unique value listed above

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,125

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    See if this does it for you:

    Sub Foo()
        Dim c As Range
        Dim rng As Range
        Dim LR As Long
            
            LR = Cells(Rows.Count, "R").End(xlUp).Row
            Set rng = Range("A1:AJ" & LR)
            
            Range("E1:E" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True
            
            For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp))
                With rng
                    .AutoFilter
                    .AutoFilter Field:=5, Criteria1:=c.Value
                    .SpecialCells(xlCellTypeVisible).Copy
                    Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
                    ActiveSheet.Paste
                End With
            Next c
            
    End Sub


    HTH,
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  5. #5
    Board Regular
    Join Date
    Jan 2010
    Posts
    125

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    Wow that is perfect. You just saved me hours of work. Thanks so much!

  6. #6
    New Member
    Join Date
    Jul 2013
    Location
    Lakewood, CO
    Posts
    2

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    I know this is an older thread. I'm doing something similiar to this, but when I copy this code into mine, I'm getting an error. My unique column is F which is called "INI Record" instead of A. This is how I modified the code to try and make it work for mine:

    Code:
    Sub Foo()
        Dim c As Range
        Dim rng As Range
        Dim LR As Long
            
            LR = Cells(Rows.Count, "R").End(xlUp).Row
            Set rng = Range("F1:FJ" & LR)
            
            Range("F1:F" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True
            
            For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp))
                With rng
                    .AutoFilter
                    .AutoFilter Field:=5, Criteria1:=c.Value
                    .SpecialCells(xlCellTypeVisible).Copy
                    Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
                    ActiveSheet.Paste
                End With
            Next c
            
    End Sub
    When I run the code, I get an error of "Run-time error '1004': Application-defined or object-defined error"

    Here is a paste of a few rows of what my spreadsheet looks like:

    Incident Ticket # Change Ticket # Build Summary Builder Action INI Record INI ID D/C Adv Opt Item # Record Name Clinic(s) Specialty BLD Date BLD Validator STG Date STG Data Courier STG Validator MST Date REF Date PRD Date PRD Data Courier PRD Validator Comments Issue ID Item Type
    356381 24926 Modified immunization and added Do not require LEVY, REID MODIFIED EAP - PROCEDURE 280208 880 TD VACCINE PRESERVATIVE FREE =>7YO IM ALL ALL 6/5/2013 6/5/2013 BERRY, JOAN MUELLER, CHARISSA 6/5/2013 6/5/2013 BERRY, JOAN MUELLER, CHARISSA 1 Item
    356381 24926 Modified immunization and added Do not require LEVY, REID MODIFIED EAP - PROCEDURE 280198 880 ROTAVIRUS VACCINE PENTAVALENT 3 DOSE ORAL ALL ALL 6/5/2013 6/5/2013 BERRY, JOAN MUELLER, CHARISSA 6/5/2013 6/5/2013 BERRY, JOAN MUELLER, CHARISSA 2 Item
    356381 24926 Modified immunization and added Do not require LEVY, REID MODIFIED EAP - PROCEDURE 280150 880 HIB PRP-OMP CONJUGATE VACCINE 3 DOSE IM ALL ALL 6/5/2013 6/5/2013 BERRY, JOAN MUELLER, CHARISSA 6/5/2013 6/5/2013 BERRY, JOAN MUELLER, CHARISSA 3 Item
    232868 24777 Change setting for Dual Mode Ordering Default to "Outpatient" MUELLER, CHARISSA MODIFIED DEP - DEPARTMENT 26602 17530 GME FAMILY MED AT MHCD 4/25/2013 6/6/2013 BERRY, JOAN MUELLER, CHARISSA 6/6/2013 BERRY, JOAN MUELLER, CHARISSA 4 Item
    356669 24964 Update follow up with correct encounter report PAGE, ANNA MODIFIED LPR - EPICCARE PROFILES 400000 10007 EH SYSTEM DEF ALL ALL 6/6/2013 6/6/2013 FRAZZINI, MICHAEL CUSHMAN, SEE 6/6/2013 FRAZZINI, MICHAEL CUSHMAN, SEE 5 Item
    356669 24964 add charge print groups to encounter report PAGE, ANNA MODIFIED LRP - REPORTS 2103000102 500 HN VISIT SUMMARY: ANTICOAGULATION CHECK ALL ALL 6/6/2013 6/6/2013 FRAZZINI, MICHAEL CUSHMAN, SEE 6/6/2013 FRAZZINI, MICHAEL CUSHMAN, SEE 6 Item
    356669 24964 add charge print groups to encounter report PAGE, ANNA MODIFIED LRP - REPORTS 20151111 500 EH AMB VISIT SUMMARY:W/O HISTORY ALL ALL 6/6/2013 6/6/2013 FRAZZINI, MICHAEL CUSHMAN, SEE 6/6/2013 FRAZZINI, MICHAEL CUSHMAN, SEE 7 Item
    24949 update LPF with Lab Orders SAYRE, CATHERINE MODIFIED LPF - PREFERENCE LISTS 210312038 HN MT SVB LAB FACILITY 6/6/2013 6/6/2013 SAYRE, CATHERINE BERRY, JOAN 6/6/2013 BERRY, JOAN SAYRE, CATHERINE 9 Item
    24963 update LPF with Lab Orders SAYRE, CATHERINE MODIFIED LPF - PREFERENCE LISTS 210311974 HN MT HRH LAB FACILITY 6/6/2001 6/6/2013 SAYRE, CATHERINE BERRY, JOAN 6/6/2013 FIMBREZ, JIM SAYRE, CATHERINE 10 Item
    317375 Added department to LWS record WEST, JEFFREY MODIFIED LWS - WORKSTATION REGISTRY 26264 manual MPF051 6/3/2013 6/3/2013 WEST, JEFFREY 11 Item
    317375 Added department to LWS record WEST, JEFFREY MODIFIED LWS - WORKSTATION REGISTRY 26265 manual MFP052 6/3/2013 6/3/2013 WEST, JEFFREY 12 Item
    317375 Added department to LWS record WEST, JEFFREY MODIFIED LWS - WORKSTATION REGISTRY 26266 manual MFP053 6/3/2013 6/3/2013 WEST, JEFFREY 13 Item
    357317, 356691, 357743 24985 update LPF with Lab Orders SAYRE, CATHERINE MODIFIED LPF - PREFERENCE LISTS 210312041 HN CO SMG LAB FACILITY PREF LIST SAINT MARY - GRAND JUNCTION, CO 6/6/2013 6/6/2013 SAYRE, CATHERINE KLEPFER, DENISE 6/6/2013 FRAZZINI, MICHAEL KLEPFER, DENISE 14 Item
    24987 Change setting to "Never require" for NDC requirement behavior MUELLER, CHARISSA MODIFIED DEP - DEPARTMENT 51001 17590 SVPN ABSAROKEE MED CL 6/6/2013 6/6/2013 BERRY, JOAN MUELLER, CHARISSA 6/6/2013 BERRY, JOAN MUELLER, CHARISSA 15 Item
    24987 Change setting to "Never require" for NDC requirement behavior MUELLER, CHARISSA MODIFIED DEP - DEPARTMENT 51006 17590 SVPN LONG TERM CARE 6/6/2013 6/6/2013 BERRY, JOAN MUELLER, CHARISSA 6/6/2013 BERRY, JOAN MUELLER, CHARISSA 16 Item

    Any help will be greatly appreciated.

    Michael M.

  7. #7
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,125

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    Welcome to the Board!

    What row does the error occur on?
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  8. #8
    New Member
    Join Date
    Jul 2013
    Location
    Lakewood, CO
    Posts
    2

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    It's erroring on
    Range("F1:F" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True
    I believe

    I did the Step into and that is the line that it stopped at.

  9. #9
    New Member
    Join Date
    Jul 2013
    Posts
    19

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    I'm having the same problem. Any suggestions?

    Thanks!

  10. #10
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,125

    Default Re: Excel VBA - Autofilter, copy, paste to new sheet

    Quote Originally Posted by mtmazar View Post
    It's erroring on I believe

    I did the Step into and that is the line that it stopped at.
    Sorry I didn't see this earlier. Take a look at your LR declaration, then your data -column R has no data, so the LR variable is set as 1. You need to pick a column that will always have data in it.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

Page 1 of 2 12 LastLast

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
  •  


DMCA.com