AB-

Thanks for the suggestion, but I keep getting a runtime error:

Run-time error '13'
Type mismatch

When I debug it takes me to this line:

If oneCell.Value = vbNullString Then 'Checks if the non empty cell actually
displays any value

"AB" wrote:

Try putting something like this in the ThisWorkbook module:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim oneCell As Range

Set oneCell = Worksheets("Sheet2").Cells(Rows.Count,
"B").End(xlUp)

Do While oneCell.Row 1'It assumes that your value always will be
in a row that's greater than 1
If oneCell.Value = vbNullString Then'Checks if the non empty
cell actually displays any value
Set oneCell = oneCell(0, 1)'Goes one row up
Else: Exit Do'there was value - that's your cell
End If
Loop
Worksheets("Sheet1").Range("A1").Value = oneCell.Value'put the
value into your target cell

End Sub
.