Help needed with simple macro

[Gladeve]

Hello

A total novice here that needs some help if possible.

I have a macro that needs a little tweaking. I originally paid for someone to write the macro but it has now stopped pulling data from one worksheet to another.

Basically I have two sheets in my workbook, one is called 'table' and one 'data' The data sheet is looking up data from the table sheet. It has been fine up until now but for some reason when I input a new row in the 'table' sheet it is just not being picked up in the 'data' sheet, it stops after row 100.

The data in the table cell starts at A1 and the last cell with any data will be D200

I have googled it but having absolutely no programming experience I do not understand it.

Am sure it is so simple to do and would probably take seconds for someone who knows what they are doing!

Here is the code:

Dim wsData As Worksheet
    Dim wsTable As Worksheet
    Dim lastRowTable As Long
    Dim lookupValue As Variant
    Dim updateValueB As Variant
    Dim updateValueC As Variant
    Dim updateValueD As Variant
    Dim changedCell As Range
   
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsTable = ThisWorkbook.Sheets("Table")
   
    lastRowTable = wsTable.Cells(wsTable.Rows.Count, "A").End(xlUp).Row
   
    If Not Intersect(Target, wsData.Columns("D")) Is Nothing And Target.Value <> "" Then
        Application.EnableEvents = False
        For Each changedCell In Target
            lookupValue = changedCell.Value
           
            Dim lookupRange As Range
            Set lookupRange = wsTable.Range("A1:A" & lastRowTable)
            Dim cell As Range
            Set cell = lookupRange.Find(lookupValue, LookIn:=xlValues)
           
            If Not cell Is Nothing Then
                updateValueB = wsTable.Cells(cell.Row, "B").Value
                updateValueC = wsTable.Cells(cell.Row, "C").Value
                updateValueD = wsTable.Cells(cell.Row, "D").Value
               
                wsData.Cells(changedCell.Row, "G").Value = updateValueB
                wsData.Cells(changedCell.Row, "H").Value = updateValueC
                wsData.Cells(changedCell.Row, "J").Value = updateValueD
            End If
        Next changedCell
        Application.EnableEvents = True
    ElseIf Not Intersect(Target, wsData.Columns("D")) Is Nothing And Target.Value = "" Then
        Application.EnableEvents = False
        For Each changedCell In Target
            wsData.Cells(changedCell.Row, "G").ClearContents
            wsData.Cells(changedCell.Row, "H").ClearContents
            wsData.Cells(changedCell.Row, "J").ClearContents
        Next changedCell
        Application.EnableEvents = True
    End If
End Sub

Any help would be greatly appreciated. If there are any questions despite having absolutely zero knowledge on the subject I will try to answer them as well as I can

Many thanks

 

[Gladeve]

In the sheet "Table" ?

oh sorry should have been clearer. In the sheet 'data' which is in the same workbook

 

[MARK858]

If the data it is looking for is past row 100 in sheet Table then it won't find it if the lookup value (the cell changed in column D of the Data sheet) isn't in column A of sheet Table in the row the data is in on sheet Table.

 

[Gladeve]

If the data it is looking for is past row 100 in sheet Table then it won't find it if the lookup value (the cell changed in column D of the Data sheet) isn't in column A of sheet Table in the row the data is in on sheet Table.

Thanks for the quick reply.
That makes sense

If you don't mind me asking, how do I go about changing this in the code?
There are 4 colums. A is the name of the product, B is the sold price, C is the cost price and D is the selling fees

I have tried to google how to rectify it but do not understand much of what is written despite the solution being there

Again, many thanks
Becci

 

[MARK858]

Changing the code isn't really necessary as long as the value you are entering in column D of the Data sheet is in column A of the Table sheet (and you have no empty cells selected), 1/2 of your code is based on this.

 

[Gladeve]

Changing the code isn't really necessary as long as the value you are entering in column D of the Data sheet is in column A of the Table sheet (and you have no empty cells selected), 1/2 of your code is based on this.

That's great, I totally understand now! I will ensure that this is the case.

Thanks for your help.
I will mark the post as solved.
Kind regards
Becci

 

[MARK858]

You're welcome, happy we could help