Добрый день. Обращаюсь к экспертному сообществу, расписываясь в своей неспособности решить проблему. Прибегаю к вашей экспертизе с большим уважением и надеждой, что сможете помочь.
В приложении пример таблицы, с которой работаю. Это списки с отметками по ряду признаков на разных листах. Списки большие и требуют аналитической работы, в том числе, с фильтрами. Листы представляют примерно такую логику: лист 1 - перечень и детальное описание каждой строки перечня, лист 2 - время действия каждой строки из перечня, лист 3 - взаимосвязь строк перечня друг с другом лист 4 - технический - сводная таблица по трём первым листам Крайне неудобно фильтровать каждый лист по идентичному признаку, - хотелось бы найти способ задавать фильтр только на первой странице, и, при переходе на другие листы файла видеть сразу ту же выборку (т.е. тот же отфильтрованный диапазон, что и на первой странице). На примере - допустим, нужно найти все строки, отмеченные по признаку (столбцу) "рыбалка". Я это делаю на первом листе и хотелось бы избежать повторной работы с фильтром на 2м и 3м листах. Чтобы после отфильтрованного первого листа на 2м и 3м отображались те же строки - про рыбалку.
Опережая вопросы - реальные листы и перечень гораздо больше, чем в примере, и совмещать их все на одном листе нецелесообразно. Нереально будет вводить данные и работать с ними. Кроме того, на странице "Сводная" такая опция - совмещение всех листов - реализована, но копируются данные из других 3 листов вручную, что крайне архаично, конечно. "Сводная" заведена только для целей всеобъемлющей аналитики, когда времени уже не жалко и сидишь всё копируешь и фильтруешь вручную.
Есть в рамках этой задачи ещё одна подзадача - как сделать так, чтобы цветные ячейки автоматически копировались с листа на лист в соответствующие поля? Даже более того - чтобы автоматизировать заполнение страницы "Сводная" - как можно автоматизировать копирование с сохранением формата на лист "Сводная" всех данных из всех других листов в отведённые ячейки?
Резюмируя, буду очень признательна, если научите копировать с листа на лист данные с сохранением формата и отфильтровывать несколько листов с идентичным перечнем, через работу с фильтром только на первом листе.
Добрый день. Обращаюсь к экспертному сообществу, расписываясь в своей неспособности решить проблему. Прибегаю к вашей экспертизе с большим уважением и надеждой, что сможете помочь.
В приложении пример таблицы, с которой работаю. Это списки с отметками по ряду признаков на разных листах. Списки большие и требуют аналитической работы, в том числе, с фильтрами. Листы представляют примерно такую логику: лист 1 - перечень и детальное описание каждой строки перечня, лист 2 - время действия каждой строки из перечня, лист 3 - взаимосвязь строк перечня друг с другом лист 4 - технический - сводная таблица по трём первым листам Крайне неудобно фильтровать каждый лист по идентичному признаку, - хотелось бы найти способ задавать фильтр только на первой странице, и, при переходе на другие листы файла видеть сразу ту же выборку (т.е. тот же отфильтрованный диапазон, что и на первой странице). На примере - допустим, нужно найти все строки, отмеченные по признаку (столбцу) "рыбалка". Я это делаю на первом листе и хотелось бы избежать повторной работы с фильтром на 2м и 3м листах. Чтобы после отфильтрованного первого листа на 2м и 3м отображались те же строки - про рыбалку.
Опережая вопросы - реальные листы и перечень гораздо больше, чем в примере, и совмещать их все на одном листе нецелесообразно. Нереально будет вводить данные и работать с ними. Кроме того, на странице "Сводная" такая опция - совмещение всех листов - реализована, но копируются данные из других 3 листов вручную, что крайне архаично, конечно. "Сводная" заведена только для целей всеобъемлющей аналитики, когда времени уже не жалко и сидишь всё копируешь и фильтруешь вручную.
Есть в рамках этой задачи ещё одна подзадача - как сделать так, чтобы цветные ячейки автоматически копировались с листа на лист в соответствующие поля? Даже более того - чтобы автоматизировать заполнение страницы "Сводная" - как можно автоматизировать копирование с сохранением формата на лист "Сводная" всех данных из всех других листов в отведённые ячейки?
Резюмируя, буду очень признательна, если научите копировать с листа на лист данные с сохранением формата и отфильтровывать несколько листов с идентичным перечнем, через работу с фильтром только на первом листе.K-girl
Я прошу прощения у уважаемого сообщества, если это где-то уже обсуждалось. Я честно поискала по сайту, но не нашла. Вероятно, потому, что не знаю, как правильно назвать обозначенную проблему. Пожалуйста, не ругайтесь на меня, если это уже где-то есть, а просто дайте ссылочку)) Спасибо!
Я прошу прощения у уважаемого сообщества, если это где-то уже обсуждалось. Я честно поискала по сайту, но не нашла. Вероятно, потому, что не знаю, как правильно назвать обозначенную проблему. Пожалуйста, не ругайтесь на меня, если это уже где-то есть, а просто дайте ссылочку)) Спасибо!K-girl
Boroda, спасибо. Ознакомилась, скачала, открыла. Ничего не поняла ((( Вижу в примере фильтруемый первый лист и второй лист только с одним фильтром. Отфильтровываю первый лист по любому столбцу - второй лист не меняется.
Udik, макросы - это что-то такое страшное, что я даже не знаю, как выглядит. Если это меня не съест, - я даже готова разобраться. Но вряд ли у вас будет терпение возиться с чайником в этих вопросах(( А без макросов - никак?
Boroda, спасибо. Ознакомилась, скачала, открыла. Ничего не поняла ((( Вижу в примере фильтруемый первый лист и второй лист только с одним фильтром. Отфильтровываю первый лист по любому столбцу - второй лист не меняется.
Udik, макросы - это что-то такое страшное, что я даже не знаю, как выглядит. Если это меня не съест, - я даже готова разобраться. Но вряд ли у вас будет терпение возиться с чайником в этих вопросах(( А без макросов - никак?K-girl
В ExCel играю плохо, но с удовольствием))
Сообщение отредактировал K-girl - Вторник, 16.02.2016, 16:28
K-girl, вот, что я наковыряла: Идею и основу для кода взяла здесь: http://www.planetaexcel.ru/techniques/2/197/ Кстати, там внизу страницы есть ссылка "что такое макросы...", почитайте, не такие уж они и страшные :) Посмотреть код можно так: ПКМ по ярлычку листа (Перечень) - Исходный текст
На листе Перечень в зеленую табличку вписываете критерии фильтра, для удобства я сделала списочек по столбцу B.
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:K11")) Is Nothing Then Application.ScreenUpdating = False On Error Resume Next For Each sh In ThisWorkbook.Sheets sh.ShowAllData Set x = sh.Cells.Find(What:="Наименование", After:=sh.Cells(1, 2), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows) lr = sh.Cells(Rows.Count, 2).End(xlUp).Row lc = sh.Cells(x.Row, Columns.Count).End(xlToLeft).Column x.Offset(0, -1).Resize(lr, lc).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion Next sh Application.ScreenUpdating = True End If End Sub
K-girl, вот, что я наковыряла: Идею и основу для кода взяла здесь: http://www.planetaexcel.ru/techniques/2/197/ Кстати, там внизу страницы есть ссылка "что такое макросы...", почитайте, не такие уж они и страшные :) Посмотреть код можно так: ПКМ по ярлычку листа (Перечень) - Исходный текст
На листе Перечень в зеленую табличку вписываете критерии фильтра, для удобства я сделала списочек по столбцу B.
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:K11")) Is Nothing Then Application.ScreenUpdating = False On Error Resume Next For Each sh In ThisWorkbook.Sheets sh.ShowAllData Set x = sh.Cells.Find(What:="Наименование", After:=sh.Cells(1, 2), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows) lr = sh.Cells(Rows.Count, 2).End(xlUp).Row lc = sh.Cells(x.Row, Columns.Count).End(xlToLeft).Column x.Offset(0, -1).Resize(lr, lc).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion Next sh Application.ScreenUpdating = True End If End Sub
Manyasha, я попробовала - после просмотра нескольких роликов по макросам - приладить ваше решение к файлу. Не получилось. Я, видимо, просто не понимаю, как можно взять файл и в него добавить макрос так, чтобы он заработал. Беру алгоритм, копирую в разработчике в VBA на каждую страницу файла. Не фильтрует одновременно все листы. ((
Сняла защиту через "безопасность", перезапустила файл. Ничего.
Как же мне понять, что я делаю не так? Вы когда с макросами начинали работать - у вас сразу всё получилось? Я одна так торможу? ((
Manyasha, я попробовала - после просмотра нескольких роликов по макросам - приладить ваше решение к файлу. Не получилось. Я, видимо, просто не понимаю, как можно взять файл и в него добавить макрос так, чтобы он заработал. Беру алгоритм, копирую в разработчике в VBA на каждую страницу файла. Не фильтрует одновременно все листы. ((
Сняла защиту через "безопасность", перезапустила файл. Ничего.
Как же мне понять, что я делаю не так? Вы когда с макросами начинали работать - у вас сразу всё получилось? Я одна так торможу? ((K-girl
Очень сомневаюсь! [/offtop] Давайте по порядку. Сначала Вам нужно подготовить файлик: на главном листе (НЕ на всех) сделайте табличку, в которую нужно вписывать критерии для фильтра. Столбцы этой таблицы должны присутствовать в фильтруемых таблицах на каждом листе. В моем примере это столбцы A:H (I, J, k - лишние, я про них забыла, можете их удалить).
Код срабатывает при изменении листа (по событию Change), значит макрос нужно скопировать в модуль листа: правой кнопкой мышки щелкайте по ярлычку главного листа (на котором Вы сделали новую таблицу) - Исходный текст. В появившееся окошко копируете мой макрос.
Это лишнее. Копируйте код только в главный лист, остальные не трогаем.
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) 'Макрос срабатывает при изменении диапазона A2:h11 'Замените A2:h11 на диапазон Вашей таблицы для критериев фильтра (без шапочки, т.е. без 1-й строки) If Not Intersect(Target, Range("A2:h11")) Is Nothing Then Application.ScreenUpdating = False On Error Resume Next 'Идем по ВСЕМ листам книги For Each sh In ThisWorkbook.Sheets 'Снимаем все фильтры sh.ShowAllData 'Ищем столбец "Наименование", чтобы определить, где распологается таблица, которую будем фильтровать 'Если у Вас столбцы иначе названы, поменяйте "Наименование" на свое название, т.к. если столбец 'Наименование на листе не будет найден, таблица (на этом листе) не будет фильтроваться ' Set x = sh.Cells.Find(What:="Наименование", After:=sh.Cells(1, 2), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows) 'Определяется последняя строка фильтруемой таблицы. 'В данном случае, последняя строка в столбце B lr = sh.Cells(Rows.Count, "B").End(xlUp).Row 'Определяем последний столбец таблицы lc = sh.Cells(x.Row, Columns.Count).End(xlToLeft).Column 'фильтруем x.Offset(0, -1).Resize(lr, lc).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion Next sh Application.ScreenUpdating = True End If End Sub
[/vba]
Если структура Вашей таблицы сильно отличается от того, что Вы показали в файле 1-го поста, сделайте файл максимально приближенный к оригиналу и покажите здесь.
Очень сомневаюсь! [/offtop] Давайте по порядку. Сначала Вам нужно подготовить файлик: на главном листе (НЕ на всех) сделайте табличку, в которую нужно вписывать критерии для фильтра. Столбцы этой таблицы должны присутствовать в фильтруемых таблицах на каждом листе. В моем примере это столбцы A:H (I, J, k - лишние, я про них забыла, можете их удалить).
Код срабатывает при изменении листа (по событию Change), значит макрос нужно скопировать в модуль листа: правой кнопкой мышки щелкайте по ярлычку главного листа (на котором Вы сделали новую таблицу) - Исходный текст. В появившееся окошко копируете мой макрос.
Это лишнее. Копируйте код только в главный лист, остальные не трогаем.
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) 'Макрос срабатывает при изменении диапазона A2:h11 'Замените A2:h11 на диапазон Вашей таблицы для критериев фильтра (без шапочки, т.е. без 1-й строки) If Not Intersect(Target, Range("A2:h11")) Is Nothing Then Application.ScreenUpdating = False On Error Resume Next 'Идем по ВСЕМ листам книги For Each sh In ThisWorkbook.Sheets 'Снимаем все фильтры sh.ShowAllData 'Ищем столбец "Наименование", чтобы определить, где распологается таблица, которую будем фильтровать 'Если у Вас столбцы иначе названы, поменяйте "Наименование" на свое название, т.к. если столбец 'Наименование на листе не будет найден, таблица (на этом листе) не будет фильтроваться ' Set x = sh.Cells.Find(What:="Наименование", After:=sh.Cells(1, 2), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows) 'Определяется последняя строка фильтруемой таблицы. 'В данном случае, последняя строка в столбце B lr = sh.Cells(Rows.Count, "B").End(xlUp).Row 'Определяем последний столбец таблицы lc = sh.Cells(x.Row, Columns.Count).End(xlToLeft).Column 'фильтруем x.Offset(0, -1).Resize(lr, lc).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion Next sh Application.ScreenUpdating = True End If End Sub
[/vba]
Если структура Вашей таблицы сильно отличается от того, что Вы показали в файле 1-го поста, сделайте файл максимально приближенный к оригиналу и покажите здесь.Manyasha
Manyasha, пробую прямо сейчас. Вопрос. А без вот этой таблички сверху никак нельзя? Просто и так мало места на экране - а так рабочая область данных совсем в полоску превращается...
Manyasha, пробую прямо сейчас. Вопрос. А без вот этой таблички сверху никак нельзя? Просто и так мало места на экране - а так рабочая область данных совсем в полоску превращается...K-girl
А ещё я не вижу, как фильтровать ваш файл. У меня никакие фильтры не отражаются там. Просто усеченных перечень - и всё. Т.е. не получается проверить собственно механизм макроса. Вот сейчас снесла в рабочей части первой страницы (для меня рабочая - это моя таблица) - фильтр. Всё обратно что было отобразилось. Добавила фильтр, отфильтровала. На 1м листе фильтруется, - на остальных - перечень остаётся тем, что внесён в вашей технической (зелёной) табличке над моей...
А ещё я не вижу, как фильтровать ваш файл. У меня никакие фильтры не отражаются там. Просто усеченных перечень - и всё. Т.е. не получается проверить собственно механизм макроса. Вот сейчас снесла в рабочей части первой страницы (для меня рабочая - это моя таблица) - фильтр. Всё обратно что было отобразилось. Добавила фильтр, отфильтровала. На 1м листе фильтруется, - на остальных - перечень остаётся тем, что внесён в вашей технической (зелёной) табличке над моей...K-girl
Макрос использует расширенный фильтр, поэтому нет стрелочек для выбора критериев. Не знаю, как их вернуть и возможно ли это. Чтобы показать все данные, просто удалите все критерии из верхней таблички.
Макрос использует расширенный фильтр, поэтому нет стрелочек для выбора критериев. Не знаю, как их вернуть и возможно ли это. Чтобы показать все данные, просто удалите все критерии из верхней таблички.Manyasha
Загадочно. Пошла смотреть ещё видео про макросы. Вдруг что-нибудь пойму))) Попутно научилась делать пайвоты. Работать с ними приходилось и раньше. Но теперь вот и сделать могу. Спасибо вам, Manyasha, за пинок к новым знаниям;)
Я, если не возражаете, ещё вернусь к вам с вопросом об этом макросе в ближайшее время. Что-то мне подсказывает, что они ещё будут;)
Загадочно. Пошла смотреть ещё видео про макросы. Вдруг что-нибудь пойму))) Попутно научилась делать пайвоты. Работать с ними приходилось и раньше. Но теперь вот и сделать могу. Спасибо вам, Manyasha, за пинок к новым знаниям;)
Я, если не возражаете, ещё вернусь к вам с вопросом об этом макросе в ближайшее время. Что-то мне подсказывает, что они ещё будут;)K-girl