Copy a specific range of cell values into an external workbook
 

Could someone please help with the following...

I have a worksheet containing a table of information and want to
export only the values of a specific range of cells into an external
workbook. The cells in the table all contain formulas, however i only
want to export those that are not blank (""). I need to export the
cell values each time the table is updated - the number of cells to be
copied will alter each time. This last part doesn't need to be
written into the macro, i would like to run it separately each time
the values are updated.

I have tried the following code, but this simply copies the cell
contents (including formats and formulas) into an external workbook.

Sub Test()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long

On Error Resume Next
Set bk = Workbooks("File Name.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("Path\File Name.xls")
End If

lRow = bk.Worksheets("Sheet1").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ThisWorkbook.Sheets("Sheet Name").Range("B1:H170").Copy _
Destination:=bk.Worksheets("Sheet Name").Cells(lRow, 1)

If bSave Then bk.Close Savechanges:=True

End Sub

Can anyone help?