[Solved] Saving New Cases

[Ahmd.HM]

Hi Sir,
thank you for your help which you provide recently, but I need some more please.
in the attached "Sch-OPD_New-Pat" >>> sheet "Main" I added new Patients data that is not exist in the sheet "LineList" (the yellow cells).
I need a macro to add the new data lines from "Main" to the "LineList" before I update the phone numbers.
so, the new macro will search for the Pat_Code, if the code doesn't exist in "LineList" it will be added in the next row ( 21>22>23...).
for example:
* sheet "Main" - range (A13 : D13) will be saved in a new row in sheet "LineList" (row21 in this situation) >>> range (A21 : D21)
after that:
* sheet "Main" - range (A16 : D16) will be saved in a new row in sheet "LineList" (row22)>>>range (A22 : D22)
and so on...

thank you so much

 

[Lutfor Rahman Shimanto]

Hi Sir,
thank you for your help which you provide recently, but I need some more please.
in the attached "Sch-OPD_New-Pat" >>> sheet "Main" I added new Patients data that is not exist in the sheet "LineList" (the yellow cells).
I need a macro to add the new data lines from "Main" to the "LineList" before I update the phone numbers.
so, the new macro will search for the Pat_Code, if the code doesn't exist in "LineList" it will be added in the next row ( 21>22>23...).
for example:
* sheet "Main" - range (A13 : D13) will be saved in a new row in sheet "LineList" (row21 in this situation) >>> range (A21 : D21)
after that:
* sheet "Main" - range (A16 : D16) will be saved in a new row in sheet "LineList" (row22)>>>range (A22 : D22)
and so on...

thank you so much

Hello AhmdHM

Thanks for reaching out and sharing your problem. You wanted a VBA code that searches for the Pat_Code in the Main sheet; if the code doesn't exist in the LineList, it will be added in the next row.

I am delighted to inform you that I have developed an Excel VBA Sub-procedure to fulfil your requirements. To automate the sub-procedure, you can call this procedure within a suitable event like the Worksheet_Change() event.
Follow these steps:

1. Press Alt+F11 to open the VBE.
2. Click on Insert followed by Module.
3. Insert the following code in the module and Run.
   

   Sub CopyMissingRows()
   
       Dim mainSheet As Worksheet
       Dim lineListSheet As Worksheet
       Dim mainLastRow As Long
       Dim lineListLastRow As Long
       Dim mainCell As Range
       Dim lineListCell As Range
       Dim searchValue As Variant
   
       Set mainSheet = ThisWorkbook.Sheets("Main")
       Set lineListSheet = ThisWorkbook.Sheets("LineList")
   
       mainLastRow = mainSheet.Cells(mainSheet.Rows.Count, "A").End(xlUp).Row
       lineListLastRow = lineListSheet.Cells(lineListSheet.Rows.Count, "A").End(xlUp).Row
   
       For Each mainCell In mainSheet.Range("A2:A" & mainLastRow)
   
           If IsNumeric(mainCell.Value) And Not IsEmpty(mainCell.Value) Then
         
               searchValue = mainCell.Value
   
               Set lineListCell = lineListSheet.Columns("A").Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
   
               If lineListCell Is Nothing Then
                   mainSheet.Range("A" & mainCell.Row & ":D" & mainCell.Row).Copy
                   lineListSheet.Range("A" & lineListLastRow + 1).PasteSpecial Paste:=xlPasteValues
                   Application.CutCopyMode = False
                   lineListLastRow = lineListLastRow + 1
               End If
         
           End If
       Next mainCell
   
       Set mainSheet = Nothing
       Set lineListSheet = Nothing
   
   End Sub

   

After running the Excel VBA Sub-procedure, you will see an output like the following GIF.

​

Hopefully, the idea will help you to reach your goal. I have attached the solution workbook. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[Ahmd.HM]

thank you so much dear

 

[Lutfor Rahman Shimanto]

thank you so much dear

Dear AhmdHM

Thanks for your appreciation. You are most welcome.

Regards
Lutfor Rahman Shimanto
ExcelDemy