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" wrote in message
...
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.

the new worksheet is automatically selected when it's copied

On 3 Cze, 15:27, "Keith Wilby" wrote:
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

...

ActiveSheet.Name= strWeekNo

HIH

On 3 Cze, 15:39, Jarek Kujawa wrote:
the new worksheet is automatically selected when it's copied

On 3 Cze, 15:27, "Keith Wilby" wrote:



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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -