Not sure what to name this subject
 

Hello:

If N is an integer, is the following syntax (&N) allowed
in workbook names, objects, etc?

Example: If I write this:

Set MYSHEET&N = Workbooks(Group&N.xls).Sheets
("SheetName").

and then I set N = 1, will VBA interpret this as:

Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName")

? If not (i.e., it doesn't like that), does anyone know
the correct syntax for something like this? Would I have
to use "..." anywhere in order for it to be correct?

Thanks,
MARTY

Not sure what to name this subject
 

You should set the value for N before using it in a statement
N=1
Set MYSHEET&N = Workbooks("Group "&N".xls").Sheets("SheetName").

Will interpret as: MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName")
Notice the space between Group and 1 and using N outside of quotes.
Mike F


"MARTY" wrote in message
...
Hello:

If N is an integer, is the following syntax (&N) allowed
in workbook names, objects, etc?

Example: If I write this:

Set MYSHEET&N = Workbooks(Group&N.xls).Sheets
("SheetName").

and then I set N = 1, will VBA interpret this as:

Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName")

? If not (i.e., it doesn't like that), does anyone know
the correct syntax for something like this? Would I have
to use "..." anywhere in order for it to be correct?

Thanks,
MARTY

Correction
 

I got a little sloppy
Set MYSHEET&N = Workbooks("Group "&N&".xls").Sheets("SheetName").

Another ampersand will follow a variable if it is in the middle of a name.
Mike F

"Mike Fogleman" wrote in message
news:cmUFc.23065$XM6.15333@attbi_s53...
You should set the value for N before using it in a statement
N=1
Set MYSHEET&N = Workbooks("Group "&N".xls").Sheets("SheetName").

Will interpret as: MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName")
Notice the space between Group and 1 and using N outside of quotes.
Mike F


"MARTY" wrote in message
...
Hello:

If N is an integer, is the following syntax (&N) allowed
in workbook names, objects, etc?

Example: If I write this:

Set MYSHEET&N = Workbooks(Group&N.xls).Sheets
("SheetName").

and then I set N = 1, will VBA interpret this as:

Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName")

? If not (i.e., it doesn't like that), does anyone know
the correct syntax for something like this? Would I have
to use "..." anywhere in order for it to be correct?

Thanks,
MARTY

Correction
 

Thank you Mike.

-----Original Message-----
I got a little sloppy
Set MYSHEET&N = Workbooks("Group "&N&".xls").Sheets
("SheetName").

Another ampersand will follow a variable if it is in the
middle of a name.
Mike F

"Mike Fogleman" wrote in
message
news:cmUFc.23065$XM6.15333@attbi_s53...
You should set the value for N before using it in a
statement
N=1
Set MYSHEET&N = Workbooks("Group "&N".xls").Sheets
("SheetName").

Will interpret as: MYSHEET1 = Workbooks
(Group1.xls).Sheets("SheetName")
Notice the space between Group and 1 and using N
outside of quotes.
Mike F


"MARTY" wrote in
message
...
Hello:

If N is an integer, is the following syntax (&N)
allowed
in workbook names, objects, etc?

Example: If I write this:

Set MYSHEET&N = Workbooks(Group&N.xls).Sheets
("SheetName").

and then I set N = 1, will VBA interpret this as:

Set MYSHEET1 = Workbooks(Group1.xls).Sheets
("SheetName")

? If not (i.e., it doesn't like that), does anyone
know
the correct syntax for something like this? Would I
have
to use "..." anywhere in order for it to be correct?

Thanks,
MARTY





.

Not sure what to name this subject
 

Marty,

You need

Set MYSHEET&N = Workbooks("Group" & N & ".xls").Sheets("SheetName")

N is a VBA variable, the book name is a string, so you need to concatenate
the values into your string.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"MARTY" wrote in message
...
Hello:

If N is an integer, is the following syntax (&N) allowed
in workbook names, objects, etc?

Example: If I write this:

Set MYSHEET&N = Workbooks(Group&N.xls).Sheets
("SheetName").

and then I set N = 1, will VBA interpret this as:

Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName")

? If not (i.e., it doesn't like that), does anyone know
the correct syntax for something like this? Would I have
to use "..." anywhere in order for it to be correct?

Thanks,
MARTY