PDA

View Full Version : create a reference to a copied worksheet



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

Kenneth Hobs
12-08-2008, 03:20 PM
After you copy the sheet:
wb.Worksheets(wb.Worksheets("Sheet3").Index+1).Name = "ANAME"

p45cal
12-09-2008, 03:11 AM
Kenneth, this'll do just fine, thanks!
regards, Pascal

p45cal
12-09-2008, 06:16 AM
Well...
after testing it, and trialling it on test workbooks all seemed fine, but when I put it into the real application and tried it, it failed; this is what I have, the result of this command in the immediate pane:
For zz = 1 to wb.sheets.count:?zz,wbdarius.sheets(zz).name,wb.sheets(zz).index:next zz
executed when stepping through the code snippet below just before the line with the comment '<<<:
1 Macros 1
2 Ops 2
3 DARIUS 3
4 Control 4
5 ListRef 5
6 References 6
7 Req Res 7
8 Coms 8
9 Map 9

After the execution of the line:
.Sheets("Req Res").Copy After:=wb.Sheets("Req Res") '<<<
in the following code, I'd expect to see the new sheet (Req Res (2)) after 'Req Res' in position 8...

With wb
Application.DisplayAlerts = False
For Each Sht In .Sheets
If Sht.Name = "Req ResOrig" Or Sht.Name = "ComsOrig" Then Sht.Delete
Next Sht
Application.DisplayAlerts = CurrentDisplayAlerts
.Sheets("Req Res").Visible = xlSheetHidden
.Sheets("Req Res").Copy After:=wb.Sheets("Req Res") '<<<
.Sheets(.Sheets("Req Res").Index + 1).Name = "Req ResOrig"
.Sheets("Coms").Visible = xlSheetHidden
.Sheets("Coms").Copy After:=.Sheets("Coms")
.Sheets(.Sheets("Coms").Index + 1).Name = "ComsOrig"
End With 'wb

This is what I actually get:
1 Macros 1
2 Ops 2
3 DARIUS 3
4 Req Res (2) 4
5 Control 5
6 ListRef 6
7 References 7
8 Req Res 8
9 Coms 9
10 Map 10

so when the subsequent line get executed I end up with:
1 Macros 1
2 Ops 2
3 DARIUS 3
4 Req Res (2) 4
5 Control 5
6 ListRef 6
7 References 7
8 Req Res 8
9 Req ResOrig 9
10 Map 10

which has renamed the sheet 'Coms' instead.
Why doesn't it copy it to the right place? Is it because all sheets are xlHidden except for the sheet called 'Darius' (they're all already hidden anyway, but I get the same result even if I skip the xlHidden line above)? I think I'll go with deleting any sheet whose name begins 'Req Res' but is longer than 7 characters, ensuring that when I do copy that sheet it should, with luck, be called 'Req Res (2)' and rename that sheet explicitly.. unless anyone has a better solution..

regards, Pascal