Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable cell references | Excel Discussion (Misc queries) | |||
VLOOKUP variable array references | Excel Discussion (Misc queries) | |||
variable cell references ???? | Excel Discussion (Misc queries) | |||
Variable Column references | Excel Worksheet Functions | |||
variable worksheet references | Excel Discussion (Misc queries) |