+ Reply to Thread
Results 1 to 5 of 5

Create new sheet for each category based on unique values in a column and copy/paste

  1. #1
    Registered User
    Join Date
    03-26-2021
    Location
    Sydney, Australia
    MS-Off Ver
    16
    Posts
    2

    Create new sheet for each category based on unique values in a column and copy/paste

    Hi all!

    I'm very new to macros/VBA and trying to slowly teach myself so would really appreciate any help on this problem. I am trying to filter for each unique value in Column D and then create a new sheet (named after this unique value) to then copy across all the relevant data for this category. I've attached some example data so you can see the layout. You'll see there are merged cells in the data (do I need to adjust something else for this?)

    My code is below but I am getting a runtime error 438 when I try to run this. It looks like the error is in this line: rngResults.SpecialCells(xlCellTypeVisible).Copy Destination:=Dest.Sheets(counter).Range("B8")

    Thank you in advance for any help - very much appreciated!!

    Sub MixedCodeAutoFilter()

    Dim wbDest As Workbook
    Dim rngFilter As Range, rngUniques As Range
    Dim cell As Range, counter As Integer
    Dim rngResults As Range 'filter range


    Set rngFilter = Range("D8", Range("D" & Rows.Count).End(xlUp))
    Set rngResults = Range("B8", Range("I" & Rows.Count).End(xlUp))

    Application.ScreenUpdating = False

    With rngFilter
    .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Set rngUniques = Range("D9", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

    ActiveSheet.ShowAllData

    End With


    Set Dest = Worksheets.Add

    For Each cell In rngUniques
    counter = counter + 1
    rngFilter.AutoFilter Field:=1, Criteria1:=cell.Value
    rngResults.SpecialCells(xlCellTypeVisible).Copy Destination:=Dest.Sheets(counter).Range("B8")
    Dest.Sheets(counter).Name = cell.Value

    Next cell
    rngFilter.Parent.AutoFilterMode = False
    Application.ScreenUpdating = True

    End Sub
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,055

    Re: Create new sheet for each category based on unique values in a column and copy/paste

    Hello Mr90,

    I've taken the liberty to re-write the code entirely as follows:-
    Please Login or Register  to view this content.
    Yes, the merged cells are just nuisance value and need to be un-merged when used in VBA. Hence, with Sydney, you'll need to copy it down another couple of rows for the code above to work correctly.
    The code will create and name new sheets based on the values in Column D and transfer all relevant rows of data to its individual sheet.

    I hope that this helps.

    Cheerio,
    vcoolio.

  3. #3
    Registered User
    Join Date
    03-26-2021
    Location
    Sydney, Australia
    MS-Off Ver
    16
    Posts
    2

    Re: Create new sheet for each category based on unique values in a column and copy/paste

    Hi vcoolio

    Thank you so much for this, really appreciated! I've copied this in and I am getting an error - "runtime error 13" in this line: "If Not Evaluate("ISREF('" & CStr(ar(i, 1)) & "'!A1)") Then"

    Do you know what could be causing this?

    Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,055

    Re: Create new sheet for each category based on unique values in a column and copy/paste

    Hello Mr90,

    I don't receive any error when testing it in your sample. Is your sample exactly the same as your actual workbook?

    I've attached your sample with the code implemented. Click on the "TEST ME" button to see how it works.

    Cheerio,
    vcoolio.

    P.S. Note: I've removed merged cells in Column C.
    Attached Files Attached Files
    Last edited by vcoolio; 03-27-2021 at 07:23 PM. Reason: Add P.S.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,532

    Cool Hi ! Try this !


    Quote Originally Posted by mr90 View Post
    I am getting a runtime error 438 when I try to run this
    Your error raises 'cause you are confusing worksheet and workbook ‼


    According to your attachment but without any merged cells and according to Excel basics - like an Excel beginner operating manually -
    a demonstration as a VBA beginner starter to paste to the Sheet1 worksheet module :

    PHP Code: 
    Sub Demo1()
             
    Application.ScreenUpdating False
        With 
    [B8].CurrentRegion
                 
    .Columns(3).AdvancedFilter xlFilterCopy, , [K1], True
                  
    [K1].CurrentRegion.Sort [K1], xlAscendingHeader:=xlYes
            
    For Each V In Range("K2", [K1].End(xlDown)).Value2
                S
    $ = V
                
    If Evaluate("ISREF('" "'!A1)"Then Sheets(S).[A1].CurrentRegion.Clear _
                                                     
    Else Sheets.Add(, Sheets(Sheets.Count)).Name S
                
    [K2].Value2 V
               
    .AdvancedFilter xlFilterCopy, [K1:K2], Sheets(S).[A1]
            
    Next
        End With
            
    [K1].CurrentRegion.Clear
            Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 03-27-2021 at 08:49 PM. Reason: typo …

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy and Paste Filtered Excel Column, Only Unique Values
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2017, 03:36 PM
  2. [SOLVED] Create new sheets and copy/paste data based on values in a column or columns
    By quintans1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-04-2015, 12:15 PM
  3. [SOLVED] Macro to copy and paste only unique values from one column to another column in same sheet
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-19-2014, 05:44 AM
  4. Copy unique values and paste to another sheet
    By GEMINI528 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2013, 10:20 AM
  5. [SOLVED] Macro To Create New Sheet (and rename it) based on unique values in column
    By himynameisiain in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-31-2013, 08:59 AM
  6. Create Workbooks based on Unique Values from a Column
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2012, 04:25 AM
  7. Create Sheets based on unique values in Column
    By Hblbs in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-20-2009, 08:25 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1