Renaming a copied sheet programatically

[Keith Wilby]

Hi.

I have an Excel spreadsheet with tabs named as Week Numbers. I'm using VBA
in MS Access to copy the previous week's sheet if one for the current week
doesn't exist. I can copy the sheet but it's given the name (for example)
"822 (2)". How do I select the new sheet in order to rename it "823"?
Here's the bit of code in question. Many thanks.

Keith.

If libSheetExists(objWkb, objSht, strWeekNo) = False Then
Dim strLastWeek As String
strLastWeek = strWeekNo - 1
Set objSht = objWkb.Worksheets(strLastWeek) 'Select the previous week's
sheet
objSht.Activate
objSht.Copy After:=Sheets(strLastWeek)
'HOW DO I SELECT THE NEW WORKSHEET?
objSht.Name = strWeekNo
End If

 

[Keith Wilby]

How do I select the new sheet in order to rename it "823"?

Doesn't matter. This is how I've done it:

objSht.Copy After:=Sheets(strLastWeek)
strLastWeek = strLastWeek & " (2)"
Set objSht = objWkb.Worksheets(strLastWeek)
objSht.Name = strWeekNo

Is there a more elegant way?

Thanks.

Keith.

 

[Jarek Kujawa]

the new worksheet is automatically selected when it's copied

 

[Jarek Kujawa]

...

ActiveSheet.Name= strWeekNo

HIH

the new worksheet is automatically selected when it's copied




- Poka¿ cytowany tekst -