Code not finding unique number causing duplication of data

[peter renton]

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/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg (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=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=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=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=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Trebor76]

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...


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

...to this should solve the issue:


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

How's the rest of code working?

Robert

[peter renton]

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

[peter renton]

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

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

[peter renton]

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

[Trebor76]

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


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

[peter renton]

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/z9dyn9n7f5z29bl/robert%20wb%20test%202.xlsm

Regards

Peter

[Trebor76]

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

Good luck with it.

Robert

[peter renton]

ok Robert

thanks for trying


Peter