Refer attachment;

For sheet "SPPA" cell "D18" value to be recorded on sheet "HO plan" cell "T7", and this is where it gets nasty, as the date rolls over on the "SPPA" sheet cell "F1", so must the macro record the new value in the next cell for sheet "HO plan", ie: If "SPPA" sheet cell "F1" change to "Mon 28 Feb", then the new value must be recorded on "HO plan" cell "U7", and the same for the other colour filled cells.

Thank you.

This will do that:

Option Explicit

Sub RecordDailyUnits()
Dim Col As Long
Dim LINES As Range, Ln As Range, LnFND As Range
Dim wsHO As Worksheet

Set wsHO = Sheets("HO plan")

With Sheets("SPPA")
Set LINES = .Range("A:A").SpecialCells(xlConstants)
Col = wsHO.Cells.Find(Day(.[F1]), LookIn:=xlValues, LookAt:=xlWhole).Column

For Each Ln In LINES
Set LnFND = wsHO.Cells.Find(Ln, LookIn:=xlValues, LookAt:=xlWhole)
Set LnFND = wsHO.Cells.Find("Booked", LnFND, LookIn:=xlValues, LookAt:=xlWhole)
If LnFND Is Nothing Then GoTo ErrorExit
wsHO.Cells(LnFND.Row, Col).Value = Ln.Offset(0, 3).Value
Set LnFND = Nothing
Next Ln
End With

Exit Sub

ErrorExit:
MsgBox "Could not find '" & Ln & "' on sheet HO Plan. Update sheet so there is a match."
End Sub


I also cleaned up some of the other macros in that wb.

Very cool, thank you JB!
Where you set
With Sheets("SPPA")
Set LINES = .Range("A:A").SpecialCells(xlConstants, do I have data which I deleted to make reading easier, the data in some cells are
Profit/Loss,
Outwork outstandingand
Cutting, when running the macro with this data in cells is macro giving me a
Data mismatcherror.

How do one add a new attachment when answering a question? I don't se anything here.

Use the GO ADVANCED button below, then the paperclip icon.

There is no GO ADVANCED button anywhere on this page!

Try clicking the QUOTE button, see if that gives you the advanced editor. If not, talk to a forum moderator.

Nothing form the QUOTE button, will talk to moderator, thank you.