Code not finding unique number causing duplication of data

Hi
I have attached a small wb

when the data is entered on sheet one and the macro is activated (i need this to be automatic but i will start a need thresd for this)
the selected data is coppied over into the master sheet, it should be checking if the reg has already been copied and if so only update
any changes into that row..what is doing is just adding another line (it was working) reg format would be letters and numbers.

I need sheet 2 used contracts to also copy data to the master sheet how do i add to the exsiting code to do that???


any help would be appreciated


regards


Peter

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi Peter,

Because ws2 has a space in it, the tab must be referenced in code within single quotes so changing this line of code...

Code:

---

lngMyRow = Evaluate("MATCH(""" & rngCell & """  ," & CStr(ws2.Name) & "!B:B,0)")

---

...to this should solve the issue:

Code:

---

lngMyRow = Evaluate("MATCH(""" & rngCell & """  ,'" & CStr(ws2.Name) & "'!B:B,0)")

---

How's the rest of code working?

Robert

Thanks for that Robert

I have just been trying it and if i click the macro without changing any thing it copies the last row again?

can i have the used cantrct sheet to copy into the master as well??

Peter

ok scrap the first line it appears to be working ok now :-)

how do i get the other sheet to copy the same way ?

Hi

Ihave some other coding in the main wb and when a put this code in it is triggering a duplicated reg checker an goes through every row on the sheet,
how/where do i need to add your code so as not to effect the others?

Peter

This will run (i.e. update the 'wstConsole' tab) only for the sheets in 'varMyArray':

Code:

---

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 3 'Commencement row number for data. Change to suit.
   
    Dim lngEndRow As Long
    Dim rngCell As Range
    Dim lngMyRow As Long
    Dim wstConsole As Worksheet
    Dim varMySheet As Variant, _
        varMyArray As Variant
       
    Application.ScreenUpdating = False
   
    varMyArray = Array("CONTRACTS", "USED CONTRACTS") 'These are the tabs to be processed to the 'wstConsole' tab. Change to suit.
   
    Set wstConsole = Worksheets("MASTER SHEET") 'Tab name for the data to be updated into. Change to suit.
   
    For Each varMySheet In varMyArray
   
        lngEndRow = Sheets(CStr(varMySheet)).Range("A:M").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Find the last row in ws1 from columns A to M (inclusive)
       
        For Each rngCell In Sheets(CStr(varMySheet)).Range("B" & lngStartRow & ":B" & lngEndRow)
            'If there's a reg number in column B of the current row, then...
            If Len(rngCell) > 0 Then
                On Error Resume Next
                    '...try and find if it's already exists in the 'wstConsole' sheet
                    lngMyRow = 0 'Initialise variable
                    'Note if the reg number becomes numeric, you'll need to remove the double quotation marks around rngCell
                    If InStr(CStr(wstConsole.Name), " ") > 0 Then
                        lngMyRow = Evaluate("MATCH(""" & rngCell & """  ,'" & CStr(wstConsole.Name) & "'!B:B,0)") 'Put single quotation marks around 'wstConsole' tab if there's a space (or spaces) in its name
                    Else
                        lngMyRow = Evaluate("MATCH(""" & rngCell & """  ," & CStr(wstConsole.Name) & "!B:B,0)")
                    End If
                On Error GoTo 0
                'If the reg number is already in 'wstConsole' then...
                If lngMyRow <> 0 Then
                    '...link the data from SHEET1 to the applicable row in wstConsole
                    wstConsole.Range("A" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("A" & rngCell.Row).Value
                    wstConsole.Range("B" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("B" & rngCell.Row).Value
                    wstConsole.Range("K" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("C" & rngCell.Row).Value
                    wstConsole.Range("L" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("D" & rngCell.Row).Value
                    wstConsole.Range("M" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("E" & rngCell.Row).Value
                    wstConsole.Range("E" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("F" & rngCell.Row).Value
                    wstConsole.Range("H" & lngMyRow).Formula = "=D" & lngMyRow & "-E" & lngMyRow
                'Else...
                Else
                    '...find the next available row in 'wstConsole' and then link the data from the 'varMySheet' to that row.
                    lngMyRow = wstConsole.Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 'Find the last row in ws2 from columns A to H (inclusive) and increment this by 1
                    wstConsole.Range("A" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("A" & rngCell.Row).Value
                    wstConsole.Range("B" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("B" & rngCell.Row).Value
                    wstConsole.Range("K" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("C" & rngCell.Row).Value
                    wstConsole.Range("L" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("D" & rngCell.Row).Value
                    wstConsole.Range("M" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("E" & rngCell.Row).Value
                    wstConsole.Range("E" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("F" & rngCell.Row).Value
                    wstConsole.Range("H" & lngMyRow).Formula = "=D" & lngMyRow & "-E" & lngMyRow
                End If
            End If
        Next rngCell
       
    Next varMySheet
   
    Application.ScreenUpdating = True

End Sub

---

Note the code goes into a standard module (the same as when you record a macro).

Robert

Hi Robert

i have attached a link to the full wb.
I have tried to add you code module 1 (there is aready a code in there) and also added a module two
but when i add y to copy nothing happens and when i run macro1 it starts the reg checker in the original code. and goes through every line.
I need the original checker so that duplications cant be done.

is there a way around this ?
https://www.dropbox.com/s/z9dyn9n7f5...0test%202.xlsm

Regards

Peter

Sorry Peter but with the workbook macro conflicting with mine I'm not sure what the answer is.

Good luck with it.

Robert

ok Robert

thanks for trying


Peter