+ Reply to Thread
Results 1 to 6 of 6

Loop Find Last instance of Values in another Worksheet - then Copy & Paste

Hybrid View

  1. 01-13-2016, 12:29 AM #1
    smartbuyer
    smartbuyer is offline
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    110

    Exclamation Loop Find Last instance of Values in another Worksheet - then Copy & Paste

    Hi,

    I have 2 files. 'Reqs Data Entry' & 'Reqs Processing'

    I would like to:
    1. Loop thru the 'Request IDs' in the Sheets ("Log") of 'Reqs Data Entry' worksheet that has Status (Column "E" - Sheet = "Log" ) of anything else except "Disregard Request"
    2. find the Request ID in the 'Reqs Processing' worksheet, Sheets ("Processing")
    3. Copy entire row from 'Reqs Processing' and paste to 'Reqs Data Entry', Sheets('Status")
    4. Delete any duplicates in 'Reqs Data Entry', Sheets('Status")

    I have attached the mock sheets with data.
    Reqs Processing.xlsx
    Reqs Data Entry.xlsx

    I browsed through the internet and had found nothing to help me get started so any help I can get will be great
    Reply With QuoteRegister To Reply
  2. 01-13-2016, 01:29 AM #2
    bulina2k
    bulina2k is offline
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Loop Find Last instance of Values in another Worksheet - then Copy & Paste

    Hi there SmartBuyer!

    Take a look at this code and tell me if it fits you:


    Option Explicit

Sub filter_search()
    Dim cel As Range, wsLog As Worksheet, wsStatus As Worksheet
    Dim proc_file As String, wsProc As Worksheet
    Dim xlProc As Workbook, found As Range, nextrow As Long
    
    Set wsLog = Workbooks("Reqs Data Entry.xlsm").Sheets("Log")
    Set wsStatus = Workbooks("Reqs Data Entry.xlsm").Sheets("Status")
    
    wsStatus.UsedRange.Offset(1).ClearContents '' you might want to un/comment this line if you want a fresh/clean start
    
    wsLog.UsedRange.AutoFilter Field:=5, Criteria1:="<>Disregard Request"
    proc_file = "C:\Users\Bogdan\Downloads\Reqs Processing.xlsx"   '''<<<<<< your Reqs Processing filepath here!
    Application.ScreenUpdating = False
    Set xlProc = Workbooks.Open(proc_file)
    Set wsProc = xlProc.Sheets("Processing")
    
    
    For Each cel In Intersect(wsLog.UsedRange.Offset(1).Resize(wsLog.UsedRange.Rows.Count - 1), wsLog.Columns(1)).SpecialCells(xlCellTypeVisible)

        nextrow = wsStatus.Cells(wsStatus.Rows.Count, 1).End(xlUp).Row + 1
        Set found = wsProc.Columns(1).Find(cel, lookat:=xlWhole)
        If Not found Is Nothing Then wsProc.Range("A" & found.Row & ":H" & found.Row).Copy wsStatus.Range("A" & nextrow)
        
    Next
    xlProc.Close 0
    wsStatus.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8), Header:=xlYes
    wsLog.UsedRange.AutoFilter
    Application.ScreenUpdating = True
    wsStatus.Activate
End Sub
    I will also attach the file:
    Attached Files Attached Files
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you
    Reply With QuoteRegister To Reply
  3. 01-14-2016, 01:36 AM #3
    smartbuyer
    smartbuyer is offline
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Loop Find Last instance of Values in another Worksheet - then Copy & Paste

    Thank you for the response bulina2k

    If my actual file 'Reqs Processing' file will have a different columns than the 'Reqs Data Entry file' how do i copy paste the data from one column in 'Reqs Processing' to a column in Reqs Data Entry' Sheets('Status')?

    should i call per column? example (not referencing on either file sent previously)
     nextrow = wsStatus.Cells(wsStatus.Rows.Count, 1).End(xlUp).Row + 1
        Set found = wsProc.Columns(1).Find(cel, lookat:=xlWhole)
        If Not found Is Nothing Then _ 

wsProc.Range("A" & found.Row).Copy wsStatus.Range("A" & nextrow)
wsProc.Range("C" & found.Row).Copy wsStatus.Range("B" & nextrow)
wsProc.Range("H" & found.Row).Copy wsStatus.Range("C" & nextrow)
wsProc.Range("J" & found.Row).Copy wsStatus.Range("D" & nextrow)

    Next
    xlProc.Close 0
    wsStatus.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8), Header:=xlYes
    wsLog.UsedRange.AutoFilter
    Application.ScreenUpdating = True
    wsStatus.Activate
    Reply With QuoteRegister To Reply
  4. 01-14-2016, 03:27 AM #4
    bulina2k
    bulina2k is offline
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Loop Find Last instance of Values in another Worksheet - then Copy & Paste

    Yes, if that works for you.
    Reply With QuoteRegister To Reply
  5. 01-14-2016, 06:06 PM #5
    smartbuyer
    smartbuyer is offline
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Loop Find Last instance of Values in another Worksheet - then Copy & Paste

    Will try that and will let you know

    Appreciate the time and the input :D
    Reply With QuoteRegister To Reply
  6. 01-14-2016, 07:30 PM #6
    smartbuyer
    smartbuyer is offline
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: Loop Find Last instance of Values in another Worksheet - then Copy & Paste

    Hi,

    I tried the code as below and was getting a "Object Variable or With block variable not set" error

        
For Each cel In Intersect(wsLog.UsedRange.Offset(2).Resize(wsLog.UsedRange.Rows.Count - 1), wsLog.Columns(1)).SpecialCells(xlCellTypeVisible)
    nextrow = wsStatus.Cells(wsStatus.Rows.Count, 1).End(xlUp).Row + 1
    Set found = wsProc.Columns(1).Find(cel, lookat:=xlWhole)
    If Not found Is Nothing Then wsProc.Range("E" & found.Row).Copy wsStatus.Range("A" & nextrow)
    wsProc.Range("B" & found.Row).Copy wsStatus.Range("B" & nextrow)
    wsProc.Range("D" & found.Row).Copy wsStatus.Range("D" & nextrow)
    wsProc.Range("C" & found.Row).Copy wsStatus.Range("E" & nextrow)
    wsProc.Range("A" & found.Row).Copy wsStatus.Range("F" & nextrow)
    Next
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Simplify: Can I even copy a worksheet from one instance of excel into another instance?
    By pglufkin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2014, 09:50 AM
  2. [SOLVED] VBA Loop through each worksheet except two and then copy paste
    By akulka58 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2014, 06:45 AM
  3. Loop through Worksheets find Value & Paste in another Worksheet, SUM values from Column
    By Myth0S in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2013, 06:45 PM
  4. Loop macro to find copy and paste value
    By PJLIGHT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-14-2013, 03:21 PM
  5. Loop to find a cell, then copy offset paste
    By mr.alexander in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-29-2013, 08:48 AM
  6. [SOLVED] Loop, Find, Copy, and Paste between multiple worksheets
    By bg_enigma1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2012, 06:40 PM
  7. Find-Loop copy & paste in an another sheet
    By gill389 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-19-2011, 06:31 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1