У Рениера есть рабочая книга с десятью листами с именами Ресурсы 1 — Ресурсы 10. Если он удалит один из этих листов (скажем, Ресурсы 3), Ренье хотел бы, чтобы оставшиеся листы были переименованы автоматически, чтобы «закрыть пробел». ,» так сказать. Таким образом, ресурсы с 4 по 10 будут автоматически переименованы в ресурсы с 3 по ресурсы 9.

Есть несколько способов решить эту задачу, используя макросы. Один из подходов, вероятно, менее автоматический, чем то, что предпочел бы Ренье, но он отлично работает:

Sub ReNameSheets()

Dim J As Integer     Dim wks As Worksheet

J = 0     For Each wks In ActiveWorkbook.Worksheets         J = J + 1         wks.Name = "TempSheet " & J     Next wks

J = 0     For Each wks In ActiveWorkbook.Worksheets         J = J + 1         wks.Name = "Resources " & J     Next wks End Sub

Этот макрос просто просматривает все рабочие листы, переименовывая их, используя соглашение, которое предпочитал Ренье. Макрос можно запускать по запросу, в любое время, когда требуется переименование рабочих листов.

Обратите внимание, что код фактически выполняет два прохода по именованию через рабочие листы. Первый — присвоить рабочим листам временные имена, а второй — присвоить им окончательные имена. Два прохода помогают избежать потенциальной проблемы при добавлении рабочих листов в книгу — например, если вы добавляете новый рабочий лист непосредственно перед Ресурсами 4, то при однопроходном переименовании новый рабочий лист будет пытаться переименовать как Ресурсы 4, что приведет к ошибка, потому что лист 5 в этот момент также будет называться Ресурсы 4. Выполняя два прохода, вы переименовываете все во что-то совершенно новое, а затем выполняете окончательную настройку имени.

Для более автоматического подхода вы можете рассмотреть возможность использования события SheetBeforeDelete для объекта Workbook. Это событие запускается (как следует из названия) непосредственно перед удалением рабочего листа. Вот пример подхода с обработкой событий:

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

Dim sPrefix As String     Dim iNum1 As Integer     Dim iNum2 As Integer     Dim wks As Worksheet

sPrefix = "Resources "

If Left(Sh.Name, Len(sPrefix)) = sPrefix Then         iNum1 = CInt(Right(Sh.Name, Len(Sh.Name) - Len(sPrefix)))

Sh.Name = "TempSheet 9999"

For Each wks In Sheets             If Left(wks.Name, Len(sPrefix)) = sPrefix Then                 iNum2 = CInt(Right(wks.Name, Len(wks.Name) - Len(Prefix)))

If iNum2 > iNum1 Then                     wks.Name = sPrefix & (iNum2 - 1)

End If             End If         Next     End If End Sub

Обработчик событий проверяет, начинается ли удаляемый лист с назначенного префикса («Ресурсы»). Если да, то он переименовывает удаляемый рабочий лист во временное имя («TempSheet 9999»).

Затем он проходит через каждый рабочий лист в книге и переименовывает любой рабочий лист, который имеет правильный префикс и более высокий номер суффикса, чем удаляемый рабочий лист.

В этом подходе есть два серьезных недостатка. Во-первых, он не справится, если вы добавите рабочие листы. Во-вторых, он будет надежно обрабатывать удаление только отдельных листов. Если вы удалите несколько листов одновременно, нумерация будет неправильной, и, по сути, макрос может дать сбой из-за способа, которым Excel обрабатывает удаления.

Если вы ожидаете довольно частого удаления нескольких листов, вы можете обойти проблему, используя другой подход. Этот основан на использовании событий SheetActivate и SheetDeactivate объекта Workbook.

' These variables are declared OUTSIDE of the events, ' so they are available globally.



Dim shName As String     Dim Avail As Variant

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

' When the worksheet is deactivated, the name of     ' that worksheet is stored in the shName variable.



shName = Sh.Name End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim wks As Worksheet     Dim J As Integer

On Error Resume Next     ' The following line will generate an error if the     ' the worksheet shName was deleted. If the error     ' occurrs, then we can trigger the renaming.

Avail = Sheets(shName).Range("A1")

If Err Then         J = 0         For Each wks In ActiveWorkbook.Worksheets             J = J + 1             wks.Name = "Resources " & J         Next wks     End If     On Error GoTo 0 End Sub

Эти обработчики событий работают, потому что событие SheetDeactivate запускается автоматически всякий раз, когда лист остается (например, когда вы активируете другой лист) или когда он удаляется. Затем запускается событие SheetActivate, которое, по сути, проверяет, доступен ли ранее деактивированный лист или нет. Если это невозможно, значит, мы знаем, что он был удален, и можем инициировать переименование всех листов.

Подход, который вы используете для переименования, полностью зависит от вас и зависит от того, как вы хотите работать со своими листами.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13572) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.