Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Замена ссылок на ячейки предыдущего листа автоматически при создании нового листа.
 
#1
04.08.2019 14:59:21
Всем привет.

Просьба помочь с дописанием макроса. По сути нужна подсказка в одной формуле/строке (надеюсь на это, во всяком случае).

Суть такая. Есть файл excel с большим количеством листов. Каждый лист - это текущая дата с разными данными за этот день. Каждый вечер в конце дня я создаю новый лист, где часть старых данных удаляю (оставляю только формулы), а часть суммарных данных оставляю, но в виде ссылок на ячейку предыдущего листа.
Т.е. значение в ячейке B1 листа 04.08.2019 - это просто ссылка на ячейку B1 листа 03.08.2019. В ячейке B1 листа 05.08.2019 - на ячейку B1 листа 04.08.2019. В ячейке B1 листа 06.08.2019 - на ячейку B1 листа 05.08.2019.

И так далее по аналогии в течении года. Таких ячеек на листе 6. Новый лист я создаю макросом автоматически по нажатии кнопки. И хочу как-то сделать, чтобы при создании нового листа эти ячейки со ссылками автоматически обновлялись.

Т.е., 04.08.2019 я создаю лист с названием 05.08.2019 и хочу, чтобы в соответствующих ячейках ссылки вида ='03.08.2019'!C16 (т.е. ссылки на ячейку предыдущего листа из листа 04.08.2019) автоматически менялись на ссылки ='04.08.2019'!C6 (т.е., чтобы в новом листе были уже ссылки на текущий, который стал предыдущим). По сути это и есть вся суть вопроса. Как я ни пробовал это сделать - не вышло. Пробовал и с помощью функций, связанных с датой, и ещё как-то - не получается.

Всю остальную часть по автоматизации я уже сделал: макросом создаю новый лист, присваиваю имя в виде завтрашней даты (правда с пятницы на понедельник приходится править вручную, но это не критично), переношу нужные данные, стираю ненужные, убираю лишние формулы на предыдущем листе - всё это нажатием одной кнопки в ленте инструментов. Вот ещё бы добавить эту фишку со ссылками

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

Приложить пример, к сожалению, не могу, ибо файлы рабочий и там блочат все сайты про excel, it и тому подобное. А дома есть только офис онлайн, без макросов. Ну в крайнем случае если без примера никак, то попробую сделать простейший пример какой-нибудь.
 
 
 
#2
04.08.2019 15:21:02
Цитата
Taga написал:
попробую сделать простейший пример какой-нибудь.
Вот это правильно! Только постарайтесь, чтобы структура примера была аналогичная оригиналу.
 
 
 
#3
04.08.2019 19:05:20
Всем привет.
Если я все правильно понял, то решение примерно такое. Будет работать только в случае запуска макроса накануне и без учета выходных, но главную проблему вроде решает, остальное можно дописать.
 
 
 
#4
04.08.2019 23:34:33
Sukhanov, спасибо! Попробую завтра и напишу, помогло или нет.

Цитата
Юрий М написал: Вот это правильно! Только постарайтесь, чтобы структура примера была аналогичная оригиналу.
Что-то вроде этого. Прошу прощения, что файл не в формате с макросами по описанным причинам.
Сделал 3 листа по числам: 03.08.2019, 04.08.2019 и 05.08.2019
В примере начальным будет лист 03.08.2019. Там данные сами по себе без ссылок (в рабочем экселе это будет 9 января, но в данном случае конкретные даты не имеют значения).
В общем, в листе 04.08.2019 все ячейки ссылаются на соответствующие ячейки из листа 03.08.2019. В листе 05.08.2019 - на соотв. ячейки, но уже из листа 04.08.2019. В следующем листе (06.08.2019) они будут ссылаться на ячейки из листа 05.08.2019. И так далее с каждым новым листом. Для чего в примере сделал не просто значения, а формулы - потому что в рабочем файле значения так же являются суммой данных из ячейки с предыдущего листа и какого-то значения с текущего.
Ну, то, что хочу сделать - уже описал. Чтобы при исполнении основного макроса вот эти ссылки на ячейки тоже менялись. Создаю новый лист (05.08.2019) и ссылки на лист 03.08.2019 поменялись на 04.08.2019. И так с каждым новым листом.
Изменено: Taga - 25.08.2019 20:28:48
 
 
 
#5
05.08.2019 07:55:37
С помощью двух летучих можно обойтись и без макросов. доп ячейка с именем предыдущего листа, может быть засунута и в остальные формулы.
По вопросам из тем форума, личку не читаю.
 
 
 
#6
05.08.2019 23:22:51
Открываю у себя файл на компе и формулы не работают почему-то. И прошу прощения за тупой вопрос, что такое две летучие?)

Sukhanov, времени было мало сегодня, толком не успел разобраться. так напрямую если вставить код в мой макрос, то не работает, надо разбираться.
-----------
У меня код примерно такой сейчас для создания нового листа и т.д. В идеале было бы туда просто добавить кусок для изменения ссылок. Буду ещё пробовать вставить кусок, который предложил Sukhanov

Код
Sub Macros1 ()
'Button combination: Ctrl+x
   Sheets(ThisWorkbook.Sheets.Count).Copy After:=Sheets(ThisWorkbook.Sheets.Count)
   ActiveSheet.Name = Format(Now + 1, "dd.mm.yyyy")

   Range("D16:E36").Select
   Selection.ClearContents

   Sheets(ActiveSheet.Index - 1).Select
   Range("D8").Select
   Selection.Value = Selection.Value

   Sheets(ActiveSheet.Index + 1).Select
End Sub
Изменено: Taga - 25.08.2019 16:19:39
 
 
 
#7
05.08.2019 23:29:01
Taga, для оформления когда есть специальная кнопка *см. скрин). Используйте её.
 
 
 
#8
05.08.2019 23:29:13
Цитата
Taga написал: И прошу прощения за тупой вопрос,
он не тупой, а вполне адекватный. Ряд летучих или VOLOTILE функций пересчитываются при каждом пересчет, не зависимо было ли изменение аргументов. Часто увлечение такими функциями приводит к задумчивости excel, при вводе любого значения, даже если он не требует расчета.

Цитата
Taga написал: формулы не работают почему-то.
очень странно может региональные настройки иные.
По вопросам из тем форума, личку не читаю.
 
 
 
#9
06.08.2019 22:50:44
БМВ, понял, спасибо!
Я пробовал дома открыть в эксель онлайн. Возможно, поэтому, хотя формулы и там должны работать.

Пока попробую макросом, вроде как, мне одна строчка из примера Sukhanov подошла, в тестовом файле работает. Попробую загнать это в рабочий и дописать код.
Если не поможет, то попробую вашей формулой. В любом случае, большое спасибо!
 
 
 
#10
06.08.2019 22:53:36
Taga, цитата - это не копия сообщения! Цитировать нужно дя чего-то, а не просто копируя предложения. Исправьте свои сообщения. Сделайте ЦИТАТЫ там, где они нужны.
 
 
 
#11
25.08.2019 14:44:31
Всем привет.

Прошу прощения, что долго не отписывал о результатах, какой-то сумбурный месяц получился.
В итоге код получился такой:
Код
Sub TOM_USD()
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   Application.Calculation = xlCalculationManual

   Sheets(ThisWorkbook.Sheets.Count).Copy After:=Sheets(ThisWorkbook.Sheets.Count) \\ создаём новый лист
   ActiveSheet.Name = Format (Now + 1, "dd.mm.yyyy")                               \\ присваиваем ему имя в виде завтрашней даты
   
   Range("D11:E26,J11:K26,C65:J69").Select
   Selection.ClearContents                                                          \\ чистим определённый диапазон ячеек от значений

   Cells(3, 3).Formula = "='" & Date & "'!C6"
   Cells(3, 4).Formula = "='" & Date & "'!D6"
   Cells(5, 9).Formula = "='" & Date & "'!R[0]C[0]+R[0]C[-1]"
   Cells(7, 6).Formula = "='" & Date & "'!R[0]C[0]+R[-2]C[0]"
   Cells(7, 7).Formula = "='" & Date & "'!R[0]C[0]+R[-2]C[0]"
   Cells(2, 12).Formula = "= R[4]C[-3] - '" & Date & "'!R[4]C[-3]"                   \\ то, что мне и было нужно, меняем ссылки в ячейках на предыдущий лист

   Sheets(ActiveSheet.Index - 1).Select
   Range ("D6").Select
   Selection.Value = Selection.Value                                   \\ переносим из 1 ячейки предыдущего листа формулу в новый лист, а там оставляем значение.

   Sheets(ActiveSheet.Index + 1).Select

   Application.CutCopyMode = 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   Application.EnableEvents = True
End Sub
Всё работает и это шикарно.  :D   Большое вам спасибо за помощь!

Сработал с небольшими доработками вариант, который предложил Sukhanov, так что ему отдельное спасибо.)
 
 
 
#12
25.08.2019 16:20:55
У модераоров вагон времени? Исправить сами бестолковое цитирование не захотели...
 
 
 
#13
25.08.2019 20:06:49
В чём бестолковость то? Я не понимаю.
 
 
 
#14
25.08.2019 20:32:34
Посмотрите на свои соощения сейчас. Цитаты удалены, сокращены... Где-то теряется смысл?

Цитата - не просто копия сообщения (да еще полная копия предыдущего!) или его части.  Цитата должна подчеркивать, упрощать понимание последующего текста. Ваши же "цитаты", наоборот, из-за своей громоздкости и неуместности только затрудняли чтение сообщений.

Цитирование не запрещается. Но цитировать, а не просто жать кнопку для ответа
 
 
 
Страницы: 1
Наверх