Combining values from multiple spreadsheets
 

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

Combining values from multiple spreadsheets
 

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
|
|
|

Combining values from multiple spreadsheets
 

Thanks for the advice.

However, I was trying to avoid that as the code will be pretty laborious to
set up.

But if I can't correct the line giving me trouble I will revert to using an
array to accumulate all the values as suggested.

Peter


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