+ Reply to Thread
Results 1 to 2 of 2

Copy data to another sheet if not found

Hybrid View

Lona Copy data to another sheet if... 07-06-2023, 01:53 AM
windknife Re: Copy data to another... 07-06-2023, 04:48 AM
  1. #1
    Registered User
    Join Date
    09-13-2021
    Location
    India
    MS-Off Ver
    365
    Posts
    36

    Post Copy data to another sheet if not found

    Hello guys,

    I am looking for a VBA code, to do the following. Can someone please help. Thank you.

    1)Compare 2 sheets (Column J of sheet "Data" Vs Column B of sheet "Expected outcome")
    2)If the cell content in Column J is not found in Column B (Case insensitive).
    3)Copy the cell content of Column J to Column B below the next available blank cell.
    4) In the next adjustment cell "No data" standard text should be entered for the newly copied data set.

    I have attached the sample workbook, the red font in the "Expected outcome" sheet should be the output.
    Attached Files Attached Files
    Last edited by Lona; 07-06-2023 at 08:46 AM.

  2. #2
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,894

    Re: Copy data to another sheet if not found

    Option Explicit
    Sub appendData()
        Dim i&, lr&, lr2&, Arr, Rng, c
        Dim ws1 As Worksheet, ws2 As Worksheet
        
        Application.ScreenUpdating = False
        
        Set ws1 = Worksheets("Data")
        Set ws2 = Worksheets("Expected outcome")
        lr = ws1.Cells(Rows.Count, "J").End(xlUp).Row
        Arr = ws1.Range("J3:J" & lr).Value
    
        For i = 1 To UBound(Arr, 1)
            lr2 = ws2.Cells(Rows.Count, "B").End(xlUp).Row
            Set Rng = ws2.Range("B6:B" & lr2)
            Set c = Rng.Find(Arr(i, 1), LookIn:=xlValues, MatchCase:=True)
            If c Is Nothing Then
                ws2.Cells(lr2 + 1, 2) = Arr(i, 1)
                ws2.Cells(lr2 + 1, 3) = "No Data"
            End If
        Next i
        ActiveSheet.Range("A1").Select
        Application.ScreenUpdating = True
        MsgBox "Finish!"
    End Sub
    Attached Files Attached Files
    Last edited by windknife; 07-06-2023 at 05:10 AM.

+ 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. [SOLVED] Look up negative values in Tables, if found copy data in that row to another sheet
    By popcorn7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2018, 01:57 AM
  2. [SOLVED] Formula to copy and data found in every fourth column on another sheet
    By headley4ever in forum Excel General
    Replies: 8
    Last Post: 11-06-2018, 02:56 PM
  3. Copy data from one sheet to another if cell value isn't found
    By JESSIER4025 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2018, 03:31 PM
  4. [SOLVED] vba to copy numerical data only in first column ,if found copy whole row to another sheet
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2017, 01:34 PM
  5. Replies: 1
    Last Post: 09-15-2014, 01:03 PM
  6. Replies: 1
    Last Post: 02-28-2013, 01:48 PM
  7. Macro to copy data from master sheet if match is found
    By shraddha5 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 02-08-2013, 02:17 AM

Tags for this Thread

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