Renaming a copied sheet programatically
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
|