![]() ![]() ' that worksheet is stored in the shName variable. ' When the worksheet is deactivated, the name of Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) ' These variables are declared OUTSIDE of the events, This one relies on the use of the SheetActivate and SheetDeactivate events of the Workbook object. If you anticipate deleting multiple worksheets quite often, then you can sidestep the issue by using a different approach. If you delete multiple worksheets at one time, the numbering will be incorrect and, in fact, the macro may crash because of the way in which Excel handles the deletions. Second, it will only reliably handle the deletion of single worksheets. First, it won't handle if you add worksheets. There are two big caveats with this approach. Then, it steps through each of the worksheets in the workbook and renames any worksheet that has the proper prefix and a higher suffix number than the worksheet that is being deleted. If it does, then it renames the worksheet being deleted to a temporary name ("TempSheet 9999"). The event handler checks to see if the worksheet being deleted begins with the designated prefix ("Resources "). INum2 = CInt(Right(wks.Name, Len(wks.Name) - Len(Prefix))) If Left(wks.Name, Len(sPrefix)) = sPrefix Then INum1 = CInt(Right(Sh.Name, Len(Sh.Name) - Len(sPrefix))) If Left(Sh.Name, Len(sPrefix)) = sPrefix Then Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object) Here's an example of an event-handler approach: This event is triggered (as its name suggests) immediately before a worksheet is deleted. By doing two passes, you rename everything to something entirely new and then do the final name setting.įor a more automatic approach, you might want to consider using the SheetBeforeDelete event for the Workbook object. The two passes helps avoid a potential problem when you add worksheets to the workbook-for instance, if you add a new worksheet just before Resources 4, then a single-pass renaming would try to rename the new worksheet as Resources 4, which would generate an error because worksheet 5 would, at that point, also be named Resources 4. The first one is to set the names of the worksheets to temporary names, and the second is to set them to final names. Note that the code actually makes two naming passes through the worksheets. ![]() The macro could be run on demand, anytime a renaming of the worksheets is desired. This macro simply steps through all the worksheets, renaming them using the convention that Renier preferred. One approach is probably less automatic than what Renier would prefer, but it works great:įor Each wks In ActiveWorkbook.Worksheets ![]() There are several ways that you can go about this task, all through the use of macros. Thus, Resources 4 through Resources 10 would automatically rename to become Resources 3 through Resources 9. If he deletes one of these worksheets (say, Resources 3), Renier would like the remaining worksheets to be renamed, automatically, to "close up the gap," so to speak. Renier has a workbook that has ten worksheets in it, named Resources 1 through Resources 10. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |