Variable workbook name references
 

I want to copy values from my active workbook to a series of open, inactive
workbooks. I know this can be done by explicitly naming each destination
workbook:

Cells(1,1).Copy
Destination:=Workbooks(€œWkbk1.xls€�).Sheets(€œ Name€�).Range("A1")

In my application, I want to synthesize the workbook names as variables from
values in a master file and replace the workbook name €œWkbk.xls€� with each
variable needed. If I try to just replace the name with a variable, I get a
€œSubscript out of range€� error. How can I do this without having to activate
each workbook in turn?

--
Al C

Variable workbook name references
 

A variable which refers to a workbook has to be declared as a Workbook
object, ex:

Dim MyBook As Excel.Workbook
Set MyBook = ActiveWorkbook

Then you can use "MyBook" in place of the workbook reference, i.e.

Workbooks("My Old Workbook").Cells(1,1).Copy MyBook.Sheets("My
Sheet").Range("A1")


Is that what you needed?

--JP

On Apr 14, 3:01 pm, Al wrote:
I want to copy values from my active workbook to a series of open, inactive
workbooks. I know this can be done by explicitly naming each destination
workbook:

Cells(1,1).Copy
Destination:=Workbooks("Wkbk1.xls").Sheets("Name") .Range("A1")

In my application, I want to synthesize the workbook names as variables from
values in a master file and replace the workbook name "Wkbk.xls" with each
variable needed. If I try to just replace the name with a variable, I get a
"Subscript out of range" error. How can I do this without having to activate
each workbook in turn?

--
Al C

Variable workbook name references
 

If I read your example correctly, the active workbook is the destination,
whereas I have the opposite case. I could do this if I knew how to declare
the inactive workbooks as objects, since I can synthesize their names. What
I want to do is:

Set MyBook = SynthesizedWorkbook
--
Al C


"JP" wrote:

A variable which refers to a workbook has to be declared as a Workbook
object, ex:

Dim MyBook As Excel.Workbook
Set MyBook = ActiveWorkbook

Then you can use "MyBook" in place of the workbook reference, i.e.

Workbooks("My Old Workbook").Cells(1,1).Copy MyBook.Sheets("My
Sheet").Range("A1")


Is that what you needed?

--JP

On Apr 14, 3:01 pm, Al wrote:
I want to copy values from my active workbook to a series of open, inactive
workbooks. I know this can be done by explicitly naming each destination
workbook:

Cells(1,1).Copy
Destination:=Workbooks("Wkbk1.xls").Sheets("Name") .Range("A1")

In my application, I want to synthesize the workbook names as variables from
values in a master file and replace the workbook name "Wkbk.xls" with each
variable needed. If I try to just replace the name with a variable, I get a
"Subscript out of range" error. How can I do this without having to activate
each workbook in turn?

--
Al C

Variable workbook name references
 

If I'm understanding you correctly you might try something like this:

For Each book In Workbooks
If book.Name < ThisWorkbook.Name Then
Debug.Print book.Name 'or rename, copy, etc
End If
Next

--
Dan


On Apr 14, 3:00*pm, Al wrote:
If I read your example correctly, the active workbook is the destination,
whereas I have the opposite case. *I could do this if I knew how to declare
the inactive workbooks as objects, since I can synthesize their names. *What
I want to do is:

Set MyBook = SynthesizedWorkbook
--
Al C



"JP" wrote:
A variable which refers to a workbook has to be declared as a Workbook
object, ex:

Dim MyBook As Excel.Workbook
Set MyBook = ActiveWorkbook

Then you can use "MyBook" in place of the workbook reference, i.e.

Workbooks("My Old Workbook").Cells(1,1).Copy MyBook.Sheets("My
Sheet").Range("A1")

Is that what you needed?

--JP

On Apr 14, 3:01 pm, Al wrote:
I want to copy values from my active workbook to a series of open, inactive
workbooks. *I know this can be done by explicitly naming each destination
workbook:

* Cells(1,1).Copy
Destination:=Workbooks("Wkbk1.xls").Sheets("Name") .Range("A1")

In my application, I want to synthesize the workbook names as variables from
values in a master file and replace the workbook name "Wkbk.xls" with each
variable needed. *If I try to just replace the name with a variable, I get a
"Subscript out of range" error. *How can I do this without having to activate
each workbook in turn?

--
Al C- Hide quoted text -

- Show quoted text -

Variable workbook name references
 

If you set your variable:
Set destWkb = Workbooks("Wkbk1.xls").Sheets(ShName)

Then:
ActiveWorkbook.Cells(1, 1).Copy destWkb.Range("A1")

Should give the same result as:

Cells(1,1).Copy
Destination:=Workbooks(€œWkbk1.xls€�).Sheets(€œ Name€�).Range("A1")


"Al" wrote:

If I read your example correctly, the active workbook is the destination,
whereas I have the opposite case. I could do this if I knew how to declare
the inactive workbooks as objects, since I can synthesize their names. What
I want to do is:

Set MyBook = SynthesizedWorkbook
--
Al C


"JP" wrote:

A variable which refers to a workbook has to be declared as a Workbook
object, ex:

Dim MyBook As Excel.Workbook
Set MyBook = ActiveWorkbook

Then you can use "MyBook" in place of the workbook reference, i.e.

Workbooks("My Old Workbook").Cells(1,1).Copy MyBook.Sheets("My
Sheet").Range("A1")


Is that what you needed?

--JP

On Apr 14, 3:01 pm, Al wrote:
I want to copy values from my active workbook to a series of open, inactive
workbooks. I know this can be done by explicitly naming each destination
workbook:

Cells(1,1).Copy
Destination:=Workbooks("Wkbk1.xls").Sheets("Name") .Range("A1")

In my application, I want to synthesize the workbook names as variables from
values in a master file and replace the workbook name "Wkbk.xls" with each
variable needed. If I try to just replace the name with a variable, I get a
"Subscript out of range" error. How can I do this without having to activate
each workbook in turn?

--
Al C

Variable workbook name references
 

These suggestions would work if I explicitly knew the name of the destination
file, but I only have it as a constructed string that changes a number of
times in the macro. I'm probably doing something wrong, but I'm not sure
where.
Let give a better example of what I'm trying to do.

Private sub workbook_open()
dim mypath as range
dim var1 as string
dim var2 as string
dim myWb as workbook
var1 =workbooks("Master.xls").sheets(1).range("A1")
var2=workbooks("Master.xls").sheets(1).range("B1")
mypath=var1 & var2 & "\Summary.xls" ' This is a valid path name of a file
that is already open.
set myWb =workbooks(mypath) ' But this doesn't work

' I have a loop with my desired copy statement and variables
' to change source and destination, including myWb as needed.
'
cells(m,n).copy destination:=myWb.sheets("xxx").range("yyy")
'
end sub
--
Al C


"JLGWhiz" wrote:

If you set your variable:
Set destWkb = Workbooks("Wkbk1.xls").Sheets(ShName)

Then:
ActiveWorkbook.Cells(1, 1).Copy destWkb.Range("A1")

Should give the same result as:

Cells(1,1).Copy
Destination:=Workbooks(€œWkbk1.xls€�).Sheets(€œ Name€�).Range("A1")


"Al" wrote:

If I read your example correctly, the active workbook is the destination,
whereas I have the opposite case. I could do this if I knew how to declare
the inactive workbooks as objects, since I can synthesize their names. What
I want to do is:

Set MyBook = SynthesizedWorkbook
--
Al C


"JP" wrote:

A variable which refers to a workbook has to be declared as a Workbook
object, ex:

Dim MyBook As Excel.Workbook
Set MyBook = ActiveWorkbook

Then you can use "MyBook" in place of the workbook reference, i.e.

Workbooks("My Old Workbook").Cells(1,1).Copy MyBook.Sheets("My
Sheet").Range("A1")


Is that what you needed?

--JP

On Apr 14, 3:01 pm, Al wrote:
I want to copy values from my active workbook to a series of open, inactive
workbooks. I know this can be done by explicitly naming each destination
workbook:

Cells(1,1).Copy
Destination:=Workbooks("Wkbk1.xls").Sheets("Name") .Range("A1")

In my application, I want to synthesize the workbook names as variables from
values in a master file and replace the workbook name "Wkbk.xls" with each
variable needed. If I try to just replace the name with a variable, I get a
"Subscript out of range" error. How can I do this without having to activate
each workbook in turn?

--
Al C