Robert,

The difficulty lies in the code referring to other procedures which you have
but we do not. One certainly seems to delete the Consol worksheet (judging
by its name), but that does not explain why the sh.Name should fail.

Perhaps if you share DeleteConsol, GetData, CopyToSh5, CopyToSh6
andCopyToSh7 macros with us we might be able to test it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert" wrote in message
...
Dear Bob after trying many many times with with possible solutions,
have to call it a day. The code still does not work. Could it
be that the original code was meant to read from closed files.
Actions that took place. Sheet "Consol" was deleted, then window
msg "The file name, Sheet or Range is invalid of:1", then runtime
error 1004. "sh.name="Consol" highlighted in yellow. A new worksheet
gets inserted at each run with the sheet numbers incrementing by 2.
Thank you for your efforts. I shall monitor for responses failing which I
shall use the original code.

Robert


"Bob Phillips" wrote:

Sub GetData_Example3()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim destrange As Range
Dim sh As Worksheet
Dim sh2 As Worksheet
Dim iLastRow As Long
Dim i As Long
Dim sFilename As String
Application.Run "DeleteConsol"
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
Application.ScreenUpdating = False
Set sh2 = Worksheets("Sheet2")
iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
sFilename = sh2.Cells(i, "A").Row
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = "Consol"
Set destrange = sh.Cells(1, 1)
GetData sFilename, "Sheet1", "A1:D6", destrange, True
Select Case Worksheets("Consol").Range("D2")
Case 1: Application.Run " "
'etc.
End Select
Next i
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert" wrote in message
...
Using new code as below. Error "i" (marked**) "not declared".
When "Dim i As Long" (????) was added, sFileName (next row)
became "not declared". Thank you for your patience, perhaps
one more try?.Please check if my ending is correct.
Sub GetData_Example3()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim destrange As Range
Dim sh As Worksheet
Dim sh2 As Worksheet
Dim iLastRow As Long

Application.Run "DeleteConsol"
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel
Files,*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
FName = Array_Sort(FName)

Application.ScreenUpdating = False
Set sh2 = Worksheets("Sheet2")
iLastRow = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row
** For i = 1 To iLastRow
sFileName = sh2.Cells(i, "A").Row
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = "Consol"
Set destrange = sh.Cells(1, 1)
'GetData sFileName, "Sheet1", "A1:D6", destrange, True
Select Case Consol!D2
Case 1: Application.Run "CopyToSh5"
Case 2: Application.Run "CopyToSh6"
Case 3: Application.Run "CopyToSh7"
'etc.
End Select
Next i
End Sub

--
Robert