+ Reply to Thread
Results 1 to 5 of 5

Copy unique value form one sheet to another sheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    Copy unique value form one sheet to another sheet

    Dear all, I have recorded a macro that is for copy unique value form one sheet to another sheet . Here is macro that I recorded , it works very well for my case but problem is that it is very slower .Would you please help me with faster macro ?
    Sub tsgmac()
    '
    ' Macro2 Macro
    '
    
        Sheets("tsg").Select
        Columns("A:A").Select
        Range("A1:A34455").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("test").Select
        Sheets("test").Name = "test"
        Range("A1").Select
        ActiveSheet.Paste
    End Sub
    ' please note that the range Range("A1:A34455") is not fixed as it may vary based on data that i will past manually

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copy unique value form one sheet to another sheet

    Hi,

    Untested in the absence of the workbook but maybe
    Sheets("tsg").Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets("tsg").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
    Sheets("test").Range("A1").PasteSpecial(xlPasteValues)
    Alternatively you could just copy column A to the Test Sheet and use the Remove Duplicates option in the Ribbon's Data area
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    Re: Copy unique value form one sheet to another sheet

    Hi Richard Buttrey , thanks for replay , yes your second option is best to Remove Duplicates form ribbon is fastest.would you please check the code for more shorter ? if not then i will work on it..
    Sub rmduplicate()
    Dim lrow As Long
    With Worksheets("TSG")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:AD" & lrow).Copy Worksheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(0, 0)
     End With
    
    Sheets("Report").Select
        Columns("A:A").Select
        Application.CutCopyMode = False
        ActiveSheet.Range("$A$1:$AD$34455").RemoveDuplicates Columns:=1, Header:=xlYes
    
    End Sub
    ' please note that the range Range("A1:A34455") is not fixed as it may vary based on data that i will past manually

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy unique value form one sheet to another sheet

    Option Explicit
    
    Sub rmduplicate()
    Dim lrow As Long
    With Worksheets("TSG")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:AD" & lrow).Copy Worksheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(0, 0)
     End With
       Sheets("Report").Range("A1:AD" & Range("A" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
    
    End Sub

  5. #5
    Forum Contributor
    Join Date
    06-08-2012
    Location
    BD
    MS-Off Ver
    Microsoft Office 2016
    Posts
    640

    Re: Copy unique value form one sheet to another sheet

    Hi AB33 , thanks for reply. it works perfectly..

+ 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. compare sheet 1 with sheet 2 and copy unique values in sheet 1
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 07:38 PM
  2. [SOLVED] Copy data from one sheet (multiple cells) to another sheet in tabular form
    By dvs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2014, 12:13 PM
  3. [SOLVED] How to copy data range form one sheet to other sheet with desire Reverse Transpose ?
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-18-2013, 12:30 PM
  4. Copy one record with subdata (Form Sheet) to Database Sheet (Collection)
    By vrgiloilo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2011, 01:12 AM
  5. [SOLVED] Copy my active sheet to a new sheet and open with an input form
    By Brad Withrow in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-05-2006, 11:00 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