Worksheet names in VBA

[Guest]

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]

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.)

 

[Guest]

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).

 

[Guest]

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

CaroleO