VBA Update fields in different tabs button function

**BridgeCat** · 10-20-2011, 05:01 AM

So I need to create a button that when pressed will update values in another sheet.

Attached I have two sheet Order and Count. What I would like if possible is a button on the Order sheet that when pressed will find the matching supplier and product name in the Count sheet and replace the "purchase" value, with the "order value".

Ie Supplier A, Product Pen, Size 1. When the button on Order sheet is pressed it would take the "purchase" value D3, and replace Counts "purchase" value F2 for that Supplier/Product/Size

In the real table you need to search across supplier, pen & size to find a unique value.

I would like it to Highlight the cell yellow after an update has been done so if the button is accidentally pressed you can flick over and just see any cells in yellow.

I hope that makes sense. Not sure if it is possible as it is far over my head!

Thanks

**Kyle123** · 10-20-2011, 05:45 AM

I think I'd go for the below:

Public Sub CopyData()
Dim RowNumber As Long
Dim columnNumber As Integer
Dim sRng As Range

Application.ScreenUpdating = False

Sheets("Counts").AutoFilterMode = False

With Sheets("Order")
    Set sRng = .Cells(1, 1).CurrentRegion.Offset(1, 0).Resize(.Cells(1, 1).CurrentRegion.Rows.Count - 1, 4)
End With

With sRng
    For RowNumber = 2 To .Rows.Count
        For columnNumber = 1 To 3
            Sheets("Counts").Cells(1, 1).CurrentRegion.AutoFilter _
                Field:=columnNumber, Criteria1:="=" & .Cells(RowNumber, columnNumber).Value
        Next columnNumber
        With Sheets("Counts").UsedRange.SpecialCells(xlCellTypeVisible)
            .Cells(.End(xlDown).Row, 6).Value = sRng.Cells(RowNumber, 4).Value
            .Cells(.End(xlDown).Row, 6).Interior.Color = vbYellow
        End With
    Next RowNumber
End With


Sheets("Counts").AutoFilterMode = False

Application.ScreenUpdating = True

End Sub