Hi:

I have a table as follows on Sheet 1:

Item No File Name Full Path Range Name Copy To Sheet CopyToLocation(Start Cell Only)
1 Alpa.xlsx C:\users\Pravin\Documents\ "Name" MasterData $a$2
2 Pravin.xlsx C:\users\Pravin\Documents\ "Name" MasterData $j$2
3 Rohin.xlsx C:\users\Pravin\Documents\ "Name" MasterData $s$2
4 Pravin.xlsx C:\users\Pravin\Documents\ "Name" Sheet1 $a$2

which lists four files all with a range called 'Name' and written the following code to pull off info from here and copy the range to the the cells specified. However having problems getting it to recognise the range name input as string as a range - shown in red.

Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As String
Dim strCopyRange As String
Dim CopyRange As Range
Dim strCellToCopyStart As String
strListSheet = "List"

On Error GoTo ErrH
Sheets(strListSheet).Select
Range("B2").Select

'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> ""

strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2).Value
strWhereToCopy = ActiveCell.Offset(0, 3).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 4), 2, 1)
strCellToCopyStart = ActiveCell.Offset(0, 4).Value

Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Set CopyRange = Range(strCopyRange)

Range(CopyRange).Select
Selection.Copy

currentWB.Activate
Sheets(strWhereToCopy).Select
Range(strCellToCopyStart).Select


Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub

ErrH:
MsgBox "It seems some file was missing. The data copy operation is not complete."
Exit Sub
End Sub