I have opend four workbooks. Now i wanted to store reference of each workbook
in an array so that i can use them later. Please see the below code, it is
throwing an error. Here i have opened a workbooks and stored it reference in
wb, now when i try to store wb in an array, it throws error.

Set arrWorkbooks(i)= wb

Hi

Maybe this will help you:

Sub test()
Dim WbArr()
Dim i As Long
ReDim WbArr(Application.Workbooks.Count)
For Each wb In Application.Workbooks
WbArr(i) = wb.Name
i = i + 1
Next
End Sub

Regards,
Per

"Store a reference of a excel workbook"
soft.com skrev i
meddelelsen ...
I have opend four workbooks. Now i wanted to store reference of each
workbook
in an array so that i can use them later. Please see the below code, it is
throwing an error. Here i have opened a workbooks and stored it reference
in
wb, now when i try to store wb in an array, it throws error.

Set arrWorkbooks(i)= wb

Hi Per Jessen, Thanks for your reply. In the below code you are storing the
name of the workbook in the array not references to the workbooks. How can i
reference a particular workbook with this array and copy the cells from that
workbook to another workbook?

"Per Jessen" wrote:

Hi

Maybe this will help you:

Sub test()
Dim WbArr()
Dim i As Long
ReDim WbArr(Application.Workbooks.Count)
For Each wb In Application.Workbooks
WbArr(i) = wb.Name
i = i + 1
Next
End Sub

Regards,
Per

"Store a reference of a excel workbook"
soft.com skrev i
meddelelsen ...
I have opend four workbooks. Now i wanted to store reference of each
workbook
in an array so that i can use them later. Please see the below code, it is
throwing an error. Here i have opened a workbooks and stored it reference
in
wb, now when i try to store wb in an array, it throws error.

Set arrWorkbooks(i)= wb

Hi

Activate the destination workbook and use this:

Workbooks(WbArr(i)).Sheets("Sheet1").Range("A1:A10 ").Copy _
Destination:= Sheets("Sheet2").Range("A1")

Regards,
Per

"Store a reference of a excel workbook"
soft.com skrev i
meddelelsen ...
Hi Per Jessen, Thanks for your reply. In the below code you are storing
the
name of the workbook in the array not references to the workbooks. How can
i
reference a particular workbook with this array and copy the cells from
that
workbook to another workbook?

"Per Jessen" wrote:

Hi

Maybe this will help you:

Sub test()
Dim WbArr()
Dim i As Long
ReDim WbArr(Application.Workbooks.Count)
For Each wb In Application.Workbooks
WbArr(i) = wb.Name
i = i + 1
Next
End Sub

Regards,
Per

"Store a reference of a excel workbook"
soft.com skrev i
meddelelsen ...
I have opend four workbooks. Now i wanted to store reference of each
workbook
in an array so that i can use them later. Please see the below code, it
is
throwing an error. Here i have opened a workbooks and stored it
reference
in
wb, now when i try to store wb in an array, it throws error.

Set arrWorkbooks(i)= wb

Or, we could use an object variable array and store references to the
workbooks themselves:

Sub test()
Dim WbArr() As Workbook, wb
Dim i As Long
ReDim WbArr(Application.Workbooks.Count)
For Each wb In Application.Workbooks
Set WbArr(i) = wb
MsgBox WbArr(i).FullName
i = i + 1
Next
End Sub

Be sure to free the memory later with
Set WbArr() = Nothing

Hope this helps,

Hutch

"Store a reference of a excel workbook" wrote:

Hi Per Jessen, Thanks for your reply. In the below code you are storing the
name of the workbook in the array not references to the workbooks. How can i
reference a particular workbook with this array and copy the cells from that
workbook to another workbook?

"Per Jessen" wrote:

Hi

Maybe this will help you:

Sub test()
Dim WbArr()
Dim i As Long
ReDim WbArr(Application.Workbooks.Count)
For Each wb In Application.Workbooks
WbArr(i) = wb.Name
i = i + 1
Next
End Sub

Regards,
Per

"Store a reference of a excel workbook"
soft.com skrev i
meddelelsen ...
I have opend four workbooks. Now i wanted to store reference of each
workbook
in an array so that i can use them later. Please see the below code, it is
throwing an error. Here i have opened a workbooks and stored it reference
in
wb, now when i try to store wb in an array, it throws error.

Set arrWorkbooks(i)= wb