Please can someone help. I am looking to export only values from one
worksheet to an external workbook. The data to be exported is in a
table format and are formulas. I need to export only those formulas
that return values (the data has been sorted so that it is easier to
export a range as blanks are at the bottom of the data table. Is
there a code that i can use that will do this for me? I will need to
run this macro several times to create a number of new sheets in a
workbook as the data in the original is updated and the number of
cells that contain values changes.

i have tried the the code below but this coipes all (including formats
and formulas):

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

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

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

If bSave Then bk.Close Savechanges:=True

End Sub

Razorboy
you were close

Change this:
ThisWorkbook.Sheets("Sheet").Range("B1:H170").Copy _
Destination:=bk.Worksheets("Sheet").Cells(lRow, 1)

(one logical line with a _ continuation)
to this

ThisWorkbook.Sheets("Sheet").Range("B1:H170").Copy
bk.Worksheets("Sheet").Cells(lRow, 1).pastespecial xlpastevalues

(two lines)

Cheers
Simon
Excel development website: www.codematic.net


Razorboy wrote:
Please can someone help. I am looking to export only values from one
worksheet to an external workbook. The data to be exported is in a
table format and are formulas. I need to export only those formulas
that return values (the data has been sorted so that it is easier to
export a range as blanks are at the bottom of the data table. Is
there a code that i can use that will do this for me? I will need to
run this macro several times to create a number of new sheets in a
workbook as the data in the original is updated and the number of
cells that contain values changes.

i have tried the the code below but this coipes all (including formats
and formulas):

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

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

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

If bSave Then bk.Close Savechanges:=True

End Sub