Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
Changing VB Component Names to match Worksheet names using VBE | Excel Programming | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming |