Worksheet names in VBA
 

Excel 2003 SR 1

Limited VBA skills

I'm recording a macro where I open two worksheets within a workbook, and
arrange them horizontally. I move Column I to K, J to C, etc from the top
workbook to the bottom one. It works fine in the macro, but I noticed that
the name of the workbook is in the VBA - i.e.
Windows("DRAWDOWN MACRO TEST.xls:2").Activate
Columns("I:I").Select
Selection.Copy
Windows("DRAWDOWN MACRO TEST.xls:1").Activate
Range("K1").Select
ActiveSheet.Paste

Can I (and how can I) remove the reference to the name of the workbook?
This is in test mode now, but when it goes into production, the names will
change each week.

TIA,

CaroleO

Worksheet names in VBA
 

Don't go through the windows collection. Just refer to the worksheet names:

worksheets("sheet1").range("I:I").copy _
destination:=worksheets("sheet2").range("K1")

(and this drops the selections at the same time.)

CaroleO wrote:

Excel 2003 SR 1

Limited VBA skills

I'm recording a macro where I open two worksheets within a workbook, and
arrange them horizontally. I move Column I to K, J to C, etc from the top
workbook to the bottom one. It works fine in the macro, but I noticed that
the name of the workbook is in the VBA - i.e.
Windows("DRAWDOWN MACRO TEST.xls:2").Activate
Columns("I:I").Select
Selection.Copy
Windows("DRAWDOWN MACRO TEST.xls:1").Activate
Range("K1").Select
ActiveSheet.Paste

Can I (and how can I) remove the reference to the name of the workbook?
This is in test mode now, but when it goes into production, the names will
change each week.

TIA,

CaroleO

--

Dave Peterson

Worksheet names in VBA
 

you can use worksheets("...").Activate By using this you can either use the
worksheets name, or "position" (kind of like using the value in a cell or
Excel's designation for that cell).

"CaroleO" wrote:

Excel 2003 SR 1

Limited VBA skills

I'm recording a macro where I open two worksheets within a workbook, and
arrange them horizontally. I move Column I to K, J to C, etc from the top
workbook to the bottom one. It works fine in the macro, but I noticed that
the name of the workbook is in the VBA - i.e.
Windows("DRAWDOWN MACRO TEST.xls:2").Activate
Columns("I:I").Select
Selection.Copy
Windows("DRAWDOWN MACRO TEST.xls:1").Activate
Range("K1").Select
ActiveSheet.Paste

Can I (and how can I) remove the reference to the name of the workbook?
This is in test mode now, but when it goes into production, the names will
change each week.

TIA,

CaroleO

Worksheet names in VBA
 

Thanks, Dave and Brent - I'll give this a try!

CaroleO

"Dave Peterson" wrote:

Don't go through the windows collection. Just refer to the worksheet names:

worksheets("sheet1").range("I:I").copy _
destination:=worksheets("sheet2").range("K1")

(and this drops the selections at the same time.)

CaroleO wrote:

Excel 2003 SR 1

Limited VBA skills

I'm recording a macro where I open two worksheets within a workbook, and
arrange them horizontally. I move Column I to K, J to C, etc from the top
workbook to the bottom one. It works fine in the macro, but I noticed that
the name of the workbook is in the VBA - i.e.
Windows("DRAWDOWN MACRO TEST.xls:2").Activate
Columns("I:I").Select
Selection.Copy
Windows("DRAWDOWN MACRO TEST.xls:1").Activate
Range("K1").Select
ActiveSheet.Paste

Can I (and how can I) remove the reference to the name of the workbook?
This is in test mode now, but when it goes into production, the names will
change each week.

TIA,

CaroleO

--

Dave Peterson