How to fetch specific data from other sheet using macro?

Hi all,

Hope all are well.

1 help please


In Serialisation PR Records file, in sheet DM Records, Column C will contain same number as in Column B in Serialisation - DM Report 2023 file. Based on this common data in the columns, column O, U and V in Serialisation PR Records file shall be fetched from Serialisation - DM Report 2023 file.


Note 1:

Both the files will have same file names and will be open while running macro.

Macro is required in Serialisation PR Records file


Where in Serialisation PR Records file,

Number under Column C is available under Column B in Serialisation - DM Report 2023 file with below conditions:

Column G = TSCO REQ

Column I = Complete Notification & Inform Requestor

Then,

1. Column U in Serialisation PR Records file shall show as "Completed", else "Pending"


2. Column O in Serialisation PR Records file shall be fetched from Column K in Serialisation - DM Report 2023 file


3. Column V in Serialisation PR Records file shall be fetched combined from Column N and O in Serialisation - DM Report 2023 file and populated in the below format (FYI: columns N and O in actual will be hidden)

23-01-2023 10:38:07 AM


Note2:

Column V in Serialisation PR Records file shall might give a problem since it automatically captures timestamp based on below macro. This auto timestamp is required only when data is selected in Column N thus capturing timestamp its in column Q while all other columns are to be removed from below macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 3 Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub
    Application.EnableEvents = False
    Me.Unprotect
    If Not Intersect(Target, Range("n:n,u:u,aa:aa")) Is Nothing Then
        If Target.Column < 18 Then
            With Target(, 4)
                .ClearContents
                .NumberFormat = "dd/mmm/yyyy  hh:mm AM/PM"
                If Target.Value <> "" Then .Value = Now
            End With
        Else
            Select Case Target
                Case "Completed", "Closed"
                    With Target(, 2)
                        .NumberFormat = "dd/mmm/yyyy  hh:mm AM/PM"
                        .Value = Now
                    End With
                Case Else
                    Target(, 2).ClearContents
            End Select
        End If
    End If
    Application.EnableEvents = True
    Me.Protect
End Sub

Note3:

Sheet PR Records contains CF and sheet DM PR Availability contains formulae with respect to sheet DM Records. Hope they run perfectly, else i will be doomed


Pass: Abc@123


Can someone please help