+ Reply to Thread
Results 1 to 4 of 4

macro to copy duplicate in column and paste in another sheet with its corresponding row

Hybrid View

  1. #1
    Registered User
    Join Date
    03-12-2014
    Location
    Tonga
    MS-Off Ver
    Excel 2007
    Posts
    10

    macro to copy duplicate in column and paste in another sheet with its corresponding row

    Hi all,
    Plese help me out.. iam searching for a macro that will find duplicate value in row A2 (id) then copy its corresponding column to a new sheet.. please refer to my attachment, and see the result i want on sheet2 and sheet3

    thank you.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: macro to copy duplicate in column and paste in another sheet with its corresponding ro

    Try
    Sub test()
        Dim dic As Object, i As Long, temp As String, e
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = 1
        With Sheets("sheet1").Cells(1).CurrentRegion
            For i = 2 To .Columns.Count
                temp = CStr(.Cells(2, i).Value)
                If Not dic.exists(temp) Then
                    Set dic(temp) = .Columns(1)
                End If
                Set dic(temp) = Union(dic(temp), .Columns(i))
            Next
        End With
        For Each e In dic
            If Not IsSheetExists(e) Then
                Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
        End If
            With Sheets(e)
                .Cells.Clear
                dic(e).Copy .Cells(1)
                .Columns.AutoFit
            End With
        Next
    End Sub
    
    Function IsSheetExists(ByVal txt As String) As Boolean
        On Error Resume Next
        IsSheetExists = Len(Sheets(txt).Name)
        On Error GoTo 0
    End Function
    Last edited by jindon; 03-13-2014 at 01:38 AM.

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: macro to copy duplicate in column and paste in another sheet with its corresponding ro

    Hi Jindon,
    maybe
    For Each e In dic
        If Not IsSheetExists(e) Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
        With Sheets(e)
     ...
    Last edited by nilem; 03-13-2014 at 01:00 AM.

  4. #4
    Registered User
    Join Date
    03-12-2014
    Location
    Tonga
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: macro to copy duplicate in column and paste in another sheet with its corresponding ro

    Thank You so much Jindon and Nilem for helping me out..... Problem has been solved.

+ 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. MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet
    By cwitty22 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-17-2013, 04:24 PM
  2. Macro to find numbers in column and copy/paste to other sheet
    By maxbeard in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2013, 11:28 AM
  3. Macro to Copy duplicate values from a column to a separate sheet
    By eemrun in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-13-2013, 05:03 PM
  4. [SOLVED] Macro to identify a unique value in a column to copy and paste value to another sheet
    By bonny24tycoon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-04-2012, 07:59 AM
  5. Search column a for duplicate data and copy entire row to duplicate sheet
    By crazyAMP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2012, 08:21 PM

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