Copy and paste data only if data has not been pasted before

[Mugalh01]

Good Excel geniuses,

2 quick question please. (a) I’m trying copy paste results data for various individuals but would like to first search the patient list (shTarget2) to see if this data has already been entered; if so, then end the macro – otherwise continue with the script

(b)Next I would like to insert a countif formula in row 45 of the newly pasted data (shTarget2) – in the new column but don’t know how to write a formula that moves with the new column added. This formula would go above the one for the date.

Help!

Private Sub CRUKPassFailAudit()

Dim wbSource As Workbook
Dim shTarget As Worksheet
Dim shTarget2 As Worksheet
Dim shSource As Worksheet
Dim shSource2 As Worksheet
Dim strFilePath As String
Dim strPath As String
Dim Folder As FileDialog
Dim myloop

    'select folder
    Set Folder = Application.FileDialog(msoFileDialogFolderPicker)
    If Folder.Show = -1 Then
    strPath = Folder.SelectedItems(1) & "\"
   
    Set shTarget = ThisWorkbook.Sheets("Pass_Fail Data")
    Set shTarget2 = ThisWorkbook.Sheets("Mdx_numbers")

            ' Get all the files from the folder
            strFilePath = Dir(strPath & "*xlsx")
       
            Do While Not strFilePath = vbNullString
       
            ' Open the file and get the source sheet
            Set wbSource = Workbooks.Open(strPath & strFilePath, 0)
            Set shSource = wbSource.Sheets("PasteResultsTST170")
            Set shSource2 = wbSource.Sheets("Coversheet")
           
            'copy data from source workbook
           
            With shTarget
            Dim lRow As Long, rng As Range, rng2 As Range
            Set rng = shSource.Range("F2:F44")
            Set rng2 = shSource2.Range("B2")
           
                       
            rng2.Copy
            shTarget.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteValues
            rng.Copy
            shTarget.Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
           
           
            'Insert Today's Date
            shTarget.Cells(47, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Date
            End With
           
                                
            With shTarget2
            Set rng2 = shSource2.Range("B2")
            lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            rng2.Copy
            shTarget2.Range("A" & lRow).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
           
            End With

            'Close the workbook and move to the next file.
            wbSource.Close False
            strFilePath = Dir$()
           
            Loop
    End If
    End Sub