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

 

[RobP]

The only thing I could ask at this point, is does your table on "Table" sheet have any data in specifically Column A after row 100 ?

As this line :

lastRowTable = wsTable.Cells(wsTable.Rows.Count, "A").End(xlUp).Row

Is specifically looking ONLY at column A to work out where the last row of data is.

Hope it helps
Rob

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Gladeve]

No problem at all.... I will do that

 

[Alex Blakenburg]

Are the values in Sheet "Table" Column A unique ?

 

[Gladeve]

The only thing I could ask at this point, is does your table on "Table" sheet have any data in specifically Column A after row 100 ?

As this line :

lastRowTable = wsTable.Cells(wsTable.Rows.Count, "A").End(xlUp).Row

Is specifically looking ONLY at column A to work out where the last row of data is.

Hope it helps
Rob

Hi Rob
Sorry for the delay.
No there is no data at all in column A after row 100
Many thanks
Becci

 

[Gladeve]

Are the values in Sheet "Table" Column A unique ?

Hi Alex
Thanks for the reply.
Yes theya re unque.
Many thanks
Becci

 

[MARK858]

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

No there is no data at all in column A after row 100

But the lookup in the code is looking to find the data in column A of sheet Table, if there is no data in column A of sheet Table past row 100 there is nothing for it to return from those rows (even if we correct the row in lastRowTable)

            Set lookupRange = wsTable.Range("A1:A" & lastRowTable)
            Dim cell As Range
            Set cell = lookupRange.Find(lookupValue, LookIn:=xlValues)

 

[Gladeve]

But the lookup in the code is looking to find the data in column A of sheet Table, if there is no data in column A of sheet Table past row 100 there is nothing for it to return from those rows

            Set lookupRange = wsTable.Range("A1:A" & lastRowTable)
            Dim cell As Range
            Set cell = lookupRange.Find(lookupValue, LookIn:=xlValues)

Hi Mark
That makes perfect sense. But, when I do input any data in row A after row 100 nothing is being returned despite present

 

[MARK858]

But, when I do input any data in row A after row 100 nothing is being returned despite present

In the sheet "Table" ?