Как сменить название листа в адресе именованного диапазона

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как сменить название листа в адресе именованного диапазона (Макросы/Sub)

Lizard

Дата: Пятница, 22.03.2019, 05:03 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 60

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Доброе время суток.
У меня вопрос про именованный диапазон.

Есть в книге несколько именованных диапазонов.
Несколько из них начинаются со слов "Диап".
У этих диапазонов - есть адреса на которые они ссылаются, и эти адреса - всегда начинаются с названия листа (например "Лист3!" ).

Как макросом - изменить первую часть адреса диапазона (имя которого начинается с "Диап") - который обозначает название листа (то есть Лист1!, Лист3 и т.д.) - на то название листа который активен в настоящий момент ?

Например - именованные диапазоны :
Диап_rt сейчас имеет адрес

Код

=Лист3!$H$8:$H$28

Диап58354 сейчас имеет адрес

Код

=Лист3!$K$5:$M$10

Но если щелкнуть по кнопке на листе1, то именованные диапазоны:
Диап_rt будет иметь адрес

Код

=Лист1!$H$8:$H$28

Диап58354 будет иметь адрес

Код

=Лист1!$K$5:$M$10

То есть адрес диапазона - частично поменяет свое название, в зависимости от того - какой лист в настоящее время - активен.

К сообщению приложен файл: 6816747.xls (88.5 Kb)

Сообщение отредактировал Lizard - Пятница, 22.03.2019, 08:42

Ответить

китин

Дата: Пятница, 22.03.2019, 07:44 | Сообщение № 2

Группа: Модераторы

Ранг: Экселист

Сообщений: 7014

Репутация: 1073 ±

Замечаний: 0% ±

Excel 2007;2010;2016

- Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Lizard

Дата: Пятница, 22.03.2019, 09:07 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 60

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

китин, поменял

Ответить

_Boroda_

Дата: Пятница, 22.03.2019, 09:25 | Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Lizard, а Вам обязательно это макросом нужно?
Если да, то примерно так можно
[vba]

Код

Sub tt()
    n1_ = "нн"
    With ActiveWorkbook.Names(n1_)
        .RefersTo = "='" & ActiveSheet.Name & "'!" & Split(.RefersTo, "!")(1)
    End With
End Sub

[/vba]
Без макроса
Имя "лист"

Код

="'"&ПСТР(ЯЧЕЙКА("filename");ПОИСК("]";ЯЧЕЙКА("filename"))+1;33)&"'!"

Любое другое имя со ссылкой на диапазон (в файле имя "йй")

Код

=ДВССЫЛ(лист&"F9:F29")

* В 2003 и раньше все было гораздо проще - в формуле имени не нежно было писать имя листа и все отлично работало. А сейчас такое не прокатывает

Хотя не, прокатывает
Пишем имя со ссылкой на диапазон вот так

Код

=ДВССЫЛ("F9:F29")

тогда оно будет работать на каждом листе с диапазоном именно этого листа (в файле имя "цц")

К сообщению приложен файл: 6816747_1.xlsm (65.7 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Lizard

Дата: Пятница, 22.03.2019, 09:34 | Сообщение № 5

Группа: Пользователи

Ранг: Участник

Сообщений: 60

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

_Boroda_, не работает.
Выдает ошибку:
Runtime error 1004: Application-defined or object-defined error.

Подсвечивает строку:
[vba]

Код

With ActiveWorkbook.Names(n1_)

[/vba]

Сейчас макрос выглядит вот так:
[vba]

Код

Sub tt()
    n1_ = "6816747.xls"
    With ActiveWorkbook.Names("n1_")
        .RefersTo = "='" & ActiveSheet.Name & "'!" & Split(.RefersTo, "!")(1)
    End With
End Sub

[/vba]