Hi all
I always have trouble using copy with PasteSpecial especially when I try to
combine it onto one line.

I am trying to extract info from a number of worksheets (each worksheet has
about 326 named ranges on 24 different sheets) to a consolidation file which
is identical in format except the range names have the word 'Summary' in
front
I am using 'Range Names' so that, in the future, if the format of the
file(s) is changed then the macros will still work!

I am trying to convert 4 lines of code into 1
The reason is that I have to repeat a similar line 326 times with 326
different range names! and I have worked out a quick way of bringing in the
line if it is on one line but a pain if over more than one.

The error I get is:

Run-time error '1004'
'Select Method of Range class failed'

I have inserted the range name 'Creditor' into the sub files and
'Summary_Creditor' into the consolidation file

Sub ImportData()
Dim fList As Variant
Dim SubsiduaryFile(10) As String
Dim Response As String
Dim i As Integer, F As Integer
Dim Subsiduary As String
Dim Combined As String
Combined = ActiveWorkbook.Name
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
While .Execute() 0 And .Execute() F
F = F + 1
SubsiduaryFile(F) = .FoundFiles(F)
Subsiduary = Right(SubsiduaryFile(F), Len(SubsiduaryFile(F)) -
Len(CurDir) - 1)
Workbooks.Open Filename:=.FoundFiles(F), UpdateLinks:=False

'REPLACE THESE 4 LINES (WHICH DO WORK)

Range("Creditor").Copy
Windows(Combined).Activate
Range("Summary_Creditor").Select
Selection.PasteSpecial Operation:=xlPasteSpecialOperationAdd

' . . . WITH THIS SINGLE LINE - which unfortunately gives me the error
mentioned above
Windows(Subsiduary).Range("Creditor").Copy
Windows(Combined).Range("Summary_Creditor"), _
Operation:=xlPasteSpecialOperationAdd

Windows(Subsiduary).Close SaveChanges:=False
Wend
End With
End Sub

Thanks in advance for any pointers that somebody may provide

Peter Bircher
Durban, Kwazulu-Natal, SA