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.
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.
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.
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.
Thank You so much Jindon and Nilem for helping me out..... Problem has been solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks