I want to be able to take the last value in a column on Sheet 2 and display
it in another spreadsheet(Sheet 1) in cell A1 whenever someone saves the
workbook. The cell I want to copy contains a formula which displays the
calculated result and if a value isn't calculated the cells display nothing.
I would like to only take the last cell in column B that has a value.

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

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
.

What's the value in that cell? (for you to know which cell has the
faulty result try in the immediate window this (once the row gets
highlighted in yellow, the error fires and you click on debug)
?onecell.address (and then hit enter) - it will give you the address
of the cell that's wrong.
There would be an error, I guess - i.e., the cell value isn't blank
and isn't a number and isn't a text but it's something like
!DIVO
#NA
or any other similar error.
If you fix the formula not to show errors, it should be fine.
In the meantime if the onecell value isn't error - post back.