Renaming a copied sheet programatically
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 |
Renaming a copied sheet programatically
"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. |
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 |
Renaming a copied sheet programatically
...
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 - |
All times are GMT +1. The time now is 05:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com