macro to put a cell value into the right place in a column

[tonywatsonhelp]

Hi Everyone,

thought this would be easy but I'm stuck for some reason.

all it is is I have a sheet called "Invoices"

In Cell G15 I have an Invoice Number
in Cell H10 I have a ref number

now in another sheet called "Records" I have a list of the Ref Numbers (Down column D), so I just want a macro to take the value in cell G15 find the row in "Records" that holds the same ref number as H10 and put the invoice number in that row of column F

So Sheet "Invoices" Cell H10 find match in Sheet "Records" Column D and add to the same row in column F value in "Invoices" cell G15

please help if you can

thanks

Tony

 

[mumps]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Records").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Records").Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:=Sheets("Invoices").Range("H10").Value
    Sheets("Records").Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible) = Sheets("Invoices").Range("G15").Value
    If Sheets("Records").AutoFilterMode = True Then Sheets("Records").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Thanks Mumps, totally different to how I was trying to get it to work, but does the job great
Tony

 

[mumps]

My pleasure.