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:

Code:

---

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

Code:

---

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

Quote:

---

Profit/Loss

---

,

Quote:

---

Outwork outstanding

---

and

Quote:

---

Cutting

---

, when running the macro with this data in cells is macro giving me a

Quote:

---

Data mismatch

---

error.

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.