Apologies for that royUK,
The code i'm tinkering with is as follows:
Sub Update_Status() End Sub Dim rFndCell As Range Dim strData As String Dim stFnd As String Dim fCol As Integer Dim sh As Worksheet Dim ws As Worksheet Set ws = Sheets("Sheet15") Set sh = Sheets("Sheet9") stFnd = ws.Range("L1").Value With sh Set rFndCell = .Range("D:D").Find(stFnd, LookIn:=xlValues) If Not rFndCell Is Nothing Then fCol = rFndCell.Column ws.Range("N1").Copy sh.Cells(6, fCol) Else 'Can't find the item MsgBox "No Find" End If End With Exit Sub
I have also done up a quick example file and attached. in the example file the columns numbers are a bit different to my original post sorry, but the same principle applies.
Much appreciated if someone could guide me in the right direction.