Importing data from another spreadsheet as a value

[DejaVue96]

Hello all, I have a macro that I use to import cells and ranges from another sheet into my current one, but instead of importing the formula, I would like only the value. I cannot get the syntax right for VALUE to work. Here is my present macro:


Sub test()

Dim wksDest As Worksheet
Dim MyPath As String
Dim MyFile As String

Application.ScreenUpdating = False

Set wksDest = ActiveWorkbook.ActiveSheet

MyPath = "\\SynServerDV\Managers\Managers\"
MyFile = "Wednesday.xls"

Workbooks.Open Filename:=MyPath & MyFile, Password:="XXXXX"
Worksheets("Main").Range("E4:I24").Copy Destination:=wksDest.Range("E22")
Worksheets("Main").Range("J26").Copy Destination:=wksDest.Range("I44")
ActiveWorkbook.Close savechanges:=False

Application.ScreenUpdating = True

End Sub


E4 to I24 are numbers so those work, but J26 is a SUM formula and instead of the sum, I get the formula imported. I know VALUE is what I need to use, but any way I set t up it gives me an error. Any help would be appreciated.

Tony

 

[Eric W]

Try:

wksDest.Range("I44") = Worksheets("Main").Range("J26").Value