What I would do is open each workbook and create a running total of the
Creditor cell values in a VB variable and when done put that total in the
Summary_Creditor cell. You can create any number of such running totals.

--
Jim
"Pete" wrote in message
...
| 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
|
|
|