#VALUE error in UDF again
 

Aivars,
I'm not certain of this, but try changing With Worksheets(1).Range("a1")
to With Worksheets("Sheet1").Range("a1")

HTH
Henry

"Aivars" wrote in message
om...
Hello,
I have a worksheet where I use an UDF with seven arguments. The values
for these arguments come from worksheet cells.

There is a button (from Forms toolbar) with the following macro
attached to it for copying over the network:

Sub CopyToNewBook()
Dim oldBook As Excel.Workbook
Dim oldSheet As Excel.Worksheet
Dim rgRange As Range
Dim newSheet As Worksheet
Dim newBook As Workbook

Application.ScreenUpdating = False
Set oldBook = ThisWorkbook
Set oldSheet = oldBook.Worksheets("Cash_Flow")

'copy sheet to new workbook
Set newBook = Workbooks.Add
Set newSheet = newBook.Worksheets("Sheet1")

oldSheet.Cells.Copy
With newBook
With Worksheets(1).Range("a1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Range("B3").Select
End With
Application.DisplayAlerts = False
.SaveAs "\\Clk_tanker_server\TANKER\Users\Akmens\No Aivara\New
Book.xls"
.Close SaveChanges:=True ''' after this statement there are
#VALUE error
End With
Application.CutCopyMode = False

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Pressing F9 restores everything back OK.
Could it be that UDF uses the arguments from cells?

Thanks in advance

Aivars