Hello Caroline,
It's very difficult to recreate the your code. Start by describing what you
have, sheet names, workbook names and full file names etc. Post your actual
code and give details about anything else we need to know. Eg presumably
"WorksheetName1" is a worksheet level name on the active sheet, what does it
refer to and what do the referenced cells contain, Names I guess

What line gives the Value error

Regards,
Peter T

"caroline" wrote in message
...
Hello,
I tried to post this question a few days ago but did not get any answer. I
am still stuck. So i try again
Getting data from a closed wbook:
it works very well with the CODE 1 below inspired from
http://www.rondebruin.nl/copy7.htm
However, I need to extract data from a cell which address varies per sheet
and has not been named. So I would have expected to use an offset function
from a named cell (see CODE 2 (below)but it does not work. It returns a
#VALUE!
any help would be greatly appreciated
Thanks a million
Caroline


CODE 1:
For i = 1 To N
Dim Name As String
Name = Range("WorksheetName1").Offset(i - 1, 0).Value

Dim mypath As String
mypath = "='" & Range("mypath").Value & "\" & "[" &
Range("WorkbookName").Value & "]" & Range("WorksheetName1").Offset(i - 1,
0).Value & "'!"

'export data
Dim CalcExpt(1 To 3) As String
'DATES
CalcExpt(1) = "YearEnd"
CalcExpt(2) = "Launchyear1"
CalcExpt(3) = "Launchyear2"

'import range
Dim CalcImpt(1 To 3) As Range
'DATES
Set CalcImpt(1) = Sheets(Name).Range("YearEnd")
Set CalcImpt(2) = Sheets(Name).Range("Launchyear1")
Set CalcImpt(3) = Sheets(Name).Range("Launchyear2")
'copy data
For j = 1 To UBound(CalcExpt)

Dim mydata As String
mydata = mypath & CalcExpt(j)

With CalcImpt(j)
.FormulaArray = mydata
.Value = .Value
End With
Next

Next


CODE 2
For i = 1 To N
Dim Name As String
Name = Range("WorksheetName1").Offset(i - 1, 0).Value

Dim mypath As String
mypath = "=OFFSET('" & Range("mypath").Value & "\" & "[" &
Range("WorkbookName").Value & "]" & Range("WorksheetName1").Offset(i - 1,
0).Value & "'!"
Dim str As String
str = ",-1,-1)"

'export data
Dim CalcExpt(1 To 1) As String
CalcExpt(1) = "Step2Cell1"

Dim CalcImpt(1 To 1) As Range
Set CalcImpt(1) = Sheets(Name).Range("Step2Cell1")

For j = 1 To UBound(CalcExpt)

Dim mydata As String
mydata = mypath & CalcExpt(j) & str

With CalcImpt(j)
.FormulaArray = mydata
.Value = .Value
End With
Next
Next