Results 1 to 7 of 7

Transfer Every Matched Entry's Entire Row to a Different Sheet

Threaded View

  1. #1
    Registered User
    Join Date
    08-28-2014
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    50

    Transfer Every Matched Entry's Entire Row to a Different Sheet

    I want to take the horse (Column T) from sheet 'This week', match it to horses in column T in sheet 'All' and then paste every instance/match from sheet 'ALL' into 'Extracted.'

    Note: this list is huge. There are over 30,000 entries in sheet 'ALL'. Usually in sheet 'ThisWeek' there is a thousand.


    the code below does what I need it to other than two things.

    1. It does not past the entire row. I need it to go all the way up to and including column JF.

    2. It does not paste every instance of the horse (Column T) in sheet 'This Week' from sheet 'All'.

    3. I know I said two. But... I don't want it to resize the columns.

    Sub Trans()
     
     Dim X, i As Long, j As Long, Y(), Dic As Object, k, Z
     
    With Worksheets("All")
        Z = .Range("a2").CurrentRegion.Value
     End With
     
      Set Dic = CreateObject("scripting.dictionary")
      Dic.CompareMode = 1
     With Dic
        For i = 2 To UBound(Z, 1)
            .Item(Z(i, 20)) = i
        Next
     End With
     
     With Worksheets("ThisWeek")
        X = .Range("a2").CurrentRegion.Value
     End With
      ReDim Y(1 To UBound(X), 1 To UBound(X, 2))
     With Dic
        For i = 2 To UBound(X)
             If .exists(X(i, 20)) Then
                k = k + 1
                For j = 1 To UBound(X, 2)
                    Y(k, j) = X(i, j)
                Next
           End If
        Next
     End With
    
     With Worksheets("Extracted")
        .UsedRange.Offset(1).ClearContents
        If k > 0 Then .Range("A2").Resize(k, UBound(Y, 2)) = Y
        .Columns.AutoFit
     End With
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Code to transfer latest entry from sheet 'Account' to sheet 'Summary' automatically
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-30-2014, 08:56 PM
  2. [SOLVED] Copy & paste entire row to different sheet based on cell entry
    By XxCMoneyxX in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-14-2013, 07:52 PM
  3. [SOLVED] [How-to] ask marco to find, cut, paste entire row to new sheet if matched?
    By hark in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 11-15-2012, 01:55 AM
  4. VBA Script for Transfer Data from Entry Sheet to Master by Using Command Button
    By sgolwalkar4ever in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-29-2012, 07:24 AM
  5. MACRO Transfer entire row based on a table in a third sheet/tab
    By eastydie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-31-2011, 04:48 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