R
Razorboy
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
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