p45cal
12-08-2008, 11:11 AM
Is there a way of doing the following (wb is a workbook object):
wb.sheets("Sheet7").copy After:=wb.sheets("Sheet3")
Activesheet.name = "AName"
with the likes of:
set ws = wb.sheets("Sheet7").copy After:=wb.sheets("Sheet3")
ws.name="AName"
Some background: The code is in workbook A, which makes a duplicate of a sheet in workbook B (wb) in workbook B, and gives the duplicate a different name (from the default given to it by the code). The sheet to be duplicated is hidden. At the moment I make it visible, copy it, rename the copy using 'Activesheet' since it is the active sheet directly after the copy command, and make them both hidden again.
I can copy a hidden sheet, but I can't give it a name using Activesheet since it isn't the active sheet after the copy since it too is hidden...
I'm trying to avoid making the sheet visible and hiding afterwards, as well as finding a more solid connection than 'ActiveSheet' to rename the new sheet.
any suggestions please anyone?
regards, Pascal
wb.sheets("Sheet7").copy After:=wb.sheets("Sheet3")
Activesheet.name = "AName"
with the likes of:
set ws = wb.sheets("Sheet7").copy After:=wb.sheets("Sheet3")
ws.name="AName"
Some background: The code is in workbook A, which makes a duplicate of a sheet in workbook B (wb) in workbook B, and gives the duplicate a different name (from the default given to it by the code). The sheet to be duplicated is hidden. At the moment I make it visible, copy it, rename the copy using 'Activesheet' since it is the active sheet directly after the copy command, and make them both hidden again.
I can copy a hidden sheet, but I can't give it a name using Activesheet since it isn't the active sheet after the copy since it too is hidden...
I'm trying to avoid making the sheet visible and hiding afterwards, as well as finding a more solid connection than 'ActiveSheet' to rename the new sheet.
any suggestions please anyone?
regards, Pascal