VBA Help - If Cell changes value, copy cell to new Sheet

[mr9393]

Hello,

I need help to set up a VBA like below please:

When a cell (A1) changes value, I want this value to be copied to the first available row in Sheet2 A column.

Cell A1 is a formula, so ideally it copies the returned value not the formula itself.


Also I would love to have a second VBA similar but in this case with a condition: if A1 formula returns text "ERROR", then I do not want it to be copied to Sheet2.

Could you help please?
Thanks a lot!!

 

[Joe4]

Welcome to the Board!

This can be a bit tricky, because the Worksheet_Calculate event procedure in Excel detects when a formula has been recalculated somewhere on your sheet, but it cannot tell which one was recalculated, only that something somewhere on the sheet was recalculated.

What exactly is the formula in cell A1?
What is causing it to change? Is one of the values that the formula using changing?
If so, how is that value being changed? Is someone manually updating a value somewhere?

 

[mr9393]

I have a set of products, which I scan with a barcode in cell A3.

Then if it matches a set of data, in A1 it displays "Product Name XXX", if not it displays "ERROR".

I would like to copy this Product Name XXX to a new Sheet, to keep track of those products that were a match.

 

[Joe4]

So, what EXACTLY is the formula in cell A1?
Can you please post it?

 

[mr9393]

By the way that would be the only formula in the sheet, so might be a possibility then to use Worksheet_Calculate?

Alternatively, if it could just copy to a new sheet the barcode number everytime an item is scanned in A3, that could also work for me. Guess that might be easier?

Thanks a lot!

 

[mr9393]

IFERROR(INDEX(SHEET1!D:D,MATCH(A3,SHEET1!F:F,0)),"ERROR")

I scan a barcode on A3, if it founds a match then it displays in A1 the product name, if not displays error

 

[Joe4]

OK, so go to the sheet with the formula in cell A1 (assuming this is "Sheet1"), right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Calculate()
    If Range("A1") <> "ERROR" Then
        Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Range("A1").Value
    End If
End Sub

Then if cell A1 is really the only formula on the sheet, as its value changes, it should automatically update column A on Sheet2, except if it returns "ERROR".