Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: ess711 от 10.01.2009, 21:41

Название: Предыдущий лист в VBA
Отправлено: ess711 от 10.01.2009, 21:41
Добрый день.

Я знаю, что похожая тема уже обсуждалась, но не смог найти примеры в архиве. Задача в следующем: Нужно чтобы данные одной ячейки копировались из данных другой в предыдущем листе. Менять руками долго, т.к. в книге 53 листа. Наименования листов от Я1 (= январь, неделя №1) до Д53 (= декабрь, неделя 53).
Подскажите плиз, как это сделать формулой или VBA.
Название: Re: Предыдущий лист в VBA
Отправлено: IKor от 10.01.2009, 22:49
Если я Вас правильно понял, то гораздо проще будет:
1. скопировать целиком лист Я4, ссылающийся на лист Я3,
2. заменить его название на Ф5,
3. выделить все его ячейки
3. в меню ПРАВКА / ЗАМЕНИТЬ (Cntr+H) заменить все вхождения текста Я3! на Я4!

Но если Вы очень хотите сделать единую универсальную формулу для всех листов, то можно воспользоваться сочетанием функций ЯЧЕЙКА("адрес";ссылка_на_ячейку) и ДВССЫЛ(текст;необязательный параметр)
Первая из этих функций вернет Вам текстовую строку с полным адресом ячейки, которую ей передали в качестве второго параметра (если ссылка будет на другой лист, то вернется также и название листа).
Эту строку нужно будет преобразовать в строку с адресом для нового листа и использовать в качестве аргумента функции ДВССЫЛ()

Преобразование строк можно осуществить с помощью текстовых функций:
НАЙТИ("!";ЯЧЕЙКА(...);1) - вернет номер символа, разделяющего имя листа и адрес ячейки на листе
НАЙТИ("]";ЯЧЕЙКА(...);1) - вернет номер символа, разделяющего имя файла и имя листа
ПСТР(текст;НАЙТИ("]";...)+1;НАЙТИ("]";...)-НАЙТИ("!";...)-Константа) - вернет имя листа (Константа зависит от того, есть ли в имени файла или листа пробелы, и если есть, то между "!" и самим именем листа будет апостроф.
С помощью функции ЗАМЕНИТЬ() потребуется подставить новые символы на соответствующие места старой строки.
СОВЕТ_1: откажитесь от "разнобуквенного" обозначения листов и используйте всегда две цифры для обозначения недели, тогда модификация формул станет существенно проще:
функция Т(1+ЗНАЧЕН(ПСТР(текст;НАЙТИ("!";...)-2-Константа;2))) вернет Вам текстовую строку, содержащую номер на единицу больщий, нежели чем номер листа ссылки.
СОВЕТ_2: Сформируйте где-нибудь список названий листов, определяйте название текущего листа, с помощью функции ПОИСКПОЗ(найденое_имя;список_названий_листов) находите его номер в списке, после чего с помощью функции ИНДЕКС(список_названий_листов;ПОИСКПОЗ(...)+1) возвращайте имя следующего листа

Таким образом вместо простой записи:
=Я03!А1
потребуется записать
=ДВССЫЛ(Сложная_формула_преобразования_текста(ЯЧЕЙКА("адрес";Я03!А1 )))

Удачи!
Надеюсь, что помог.
Название: Re: Предыдущий лист в VBA
Отправлено: Шпец Докапыч от 10.01.2009, 23:02
Цитата: IKor от 10.01.2009, 22:49
=ДВССЫЛ(Сложная_формула_преобразования_текста(ЯЧЕЙКА("адрес";Я03!А1 )))

Я было тоже хотел сначала сообразить через ДВССЫЛ(ЯЧЕЙКА()), но не придумал как сослаться на предыдущий лист. К тому же, почему-то решил, что у ess711 (https://forum.msexcel.ru/profiles/ess711-u4790.html) именно ОДНА ячейка со ссылкой. Короче, это предыстория.

Поэтому решил пойти другим путём (через ПФ). Вот инструкция:
1) Бросаем в обычный модуль код пользовательской функции:
Function СОСЕДЛИСТ(Ссылка As Range, Смещение As Long)
   Application.Volatile
   СОСЕДЛИСТ = Sheets(Ссылка.Parent.Index + Смещение).Range(Ссылка.Address).Value
End Function


2) Удерживая Shift, щёлкаем по ярлычкам крайних листов "Я1" и "Д53". В заголовке окна должно дописаться "...[Группа]"

3) Пишем формулу, например, в "A1":
Название: Re: Предыдущий лист в VBA
Отправлено: ess711 от 11.01.2009, 06:12
Спасибо, через код все работает.

А можно через код сделать чтобы,

например ячейка A1 листа Я1 книги 1 ссылалась на ячейку B1 листа Я1 в книге 2. Т.е автоматом для всех листов. Обязательное условие чтобы имена листов совпадали, хотя это наверное не важно, так как последовательность листов в книгах одинакова.

Название: Re: Предыдущий лист в VBA
Отправлено: Goorito от 08.04.2019, 08:13
удален
Название: Re: Предыдущий лист в VBA
Отправлено: _Boroda_ от 09.04.2019, 09:19
Приложите файл к сообщению. Об этом в Правилах форума написано
Название: Re: Предыдущий лист в VBA
Отправлено: Goorito от 11.04.2019, 06:51
Цитата: _Boroda_ от 09.04.2019, 09:19
Приложите файл к сообщению. Об этом в Правилах форума написано

как это сделать, научите.
Не смог найти кнопку для вложений
Название: Re: Предыдущий лист в VBA
Отправлено: vikttur от 11.04.2019, 09:07
Под полем для ввода сообщения:
Вложения и другие параметры - Выберите файл
Название: Re: Предыдущий лист в VBA
Отправлено: Goorito от 12.04.2019, 08:51
удален, не актуально
Название: Re: Предыдущий лист в VBA
Отправлено: boa от 15.04.2019, 10:55
Здесь (https://www.excel-vba.ru/forum/index.php?topic=5919.msg31419#msg31419) ответ на ваш вопрос
в принципе, тоже, что тут (https://forum.msexcel.ru/index.php/topic,1228.msg6933.html#msg6933) написано.