Vba excel разделить макрос

Обновлено: 04.07.2024

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

А именно:
Фильтр по ячейке Дата 2 (J) и Договор (I).
Диапазон сохранения данных в новый файл со столбца A, по столбец I(включительно).
Как должен выглядеть финальный результат: создаются файлы с названием(к примеру) "08.06.2020 55.34-ПП (1).xlsx", "08.06.2020 55.34-ПП (2).xlsx", "09.06.2020 55.34-ПП (2).xlsx" и так далее, в которых содержится информация из диапазона A:I. В файле с названием "08.06.2020 55.34-ПП (1).xlsx" должны содержатся все строки где Дата 2 (J) и Договор (I) соответствуют тому, что в названии.

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

exPower

Автор - exPower
Дата добавления - 08.10.2020 в 13:02

В программе Excel присутствует кнопка для разъединения объединенных ячеек таблицы на закладке: «ГЛАВНАЯ»-«Выравнивание»-«Отменить объединение ячеек». Но что, если эту операцию нужно выполнять многократно, да еще и после нее заполнять данными ново созданные ячейки. Реализовать данную задачу вручную – это весьма затратное занятие по времени и силам. Здесь рационально воспользоваться макросом.

Макрос для разъединения объединенных ячеек в Excel

Допустим у нас уже имеется вполне читабельная таблица списка заказов, в которой имеются объединенные ячейки в столбце «Год». Пример, такой таблицы изображен ниже на рисунке:

Но нам необходимо преобразовать данную таблицу в стандартный формат, например, для создания отчета на основе сводной таблицы. Для этого откроем редактор Visual Basic (ALT+F11):

И вставим новый стандартный модуль используя инструмент в редакторе: «Insert»-«Module». А после чего запишем в модуль VBA код макроса для разъединения объединенных ячеек:

Sub RazdelitVstavit()
Dim adres As String
adres = ActiveCell.MergeArea.Address
If adres <> ActiveCell.Address Then
ActiveCell.UnMerge
ActiveCell.Copy
ActiveSheet.Paste ActiveSheet.Range(adres)
Application.CutCopyMode = False
End If
End Sub

Если мы хотим отменить объединение ячеек в столбце «Год» и заполнить созданные ячейки соответствующими значениями (годами), тогда перейдите на одну большую объединенную ячейку B2 и запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«RazdelitVstavit»-«Выполнить».

В данном VBA коде макроса используется только одна переменная. Она хранит в себе адрес диапазона ячеек B2:B15 которые охватывает одна активная объединенная ячейка.

Адрес активной ячейки отображается в поле «Имя» (напротив строки формул Excel). Но там не отображается полный адрес объединенной ячейки.

Для пользователя в поле «Имя» будет адрес отображаться одинаково, но в макросе их можно различить с помощью методов объекта ActiveCell.MergeArea.Addres. В зависимости какой тип активных ячеек будет возвращен тип адреса – одна ячейка или диапазон. Если активная ячейка не является объединенной, тогда в переменной будет храниться только адрес одной активной ячейки, а не целого диапазона. Далее макрос проверяет является ли текущая активная ячейка – объединенной, с помощью сравнения двух способов получения адреса для одной и той же активной ячейки. Тот способ, который передал адрес в переменную из метода объекта ActiveCell.MergeArea.Addres и обычный – ActiveCell.Addres. Если адрес в переменной и адрес получен обычным способом не совпадает, значит она является объединенной и код выполняется дальше.

С помощью метода объекта ActiveCell.UnMerge выполняется разъединение объединенной активной ячейки. Далее копируется ее содержимое и заполняется диапазон на листе, адрес которого получен из переменной, его же ранее содержала в себе объединенная активная ячейка. После копирования значения для объекта CutCopyMode устанавливается свойство False, чтобы прекратить процесс копирования. В результате таблица листа заказов будет иметь такой же вид как показано ниже на рисунке:

Данный макрос позволяет разъединить объединенные ячейки, которые используют любое направление объединения: как по вертикали, так и полгоризонтали. Ее значение будет одинаково вставлено во все ячейки, созданные после разъединения.

Внимание! Объединенная ячейка может содержать в качестве значения формулы. В такие случаи после запуска макроса эта формула будет вставлена во все ячейки созданных в результате отмены объединения только из относительных ссылок в адресах, поскольку в переменной не будет символа $ необходимого для абсолютного или смешанного адреса.

Как разъединить объединенные ячейки сразу в нескольких диапазонах

Если мы хотим, чтобы данный макрос можно было применить одновременно для нескольких объединенных ячеек в выделенном диапазоне, тогда добавим еще одну переменную, которая дополнит код счетчиком цикла:

Создадим цикл, который будет перемещаться по всем выделенным объединенным ячейкам:

For i = 1 To Selection.Count

В конце кода не забудем добавить конец цикла:

Вместо ссылки на активную ячейку Active.Cell теперь будем использовать ссылку на очередную по счету ячейку в выделенном диапазоне: Selection.(i). Полная версия усовершенствованного макроса выглядит следующим образом:

Sub RazdelitVstavit()
Dim adres As String
Dim i As Long
For i = 1 To Selection.Count
adres = Selection(i).MergeArea.Address
If adres <> Selection(i).Address Then
Selection(i).UnMerge
Selection(i).Copy
ActiveSheet.Paste ActiveSheet.Range(adres)
Application.CutCopyMode = False
End If
Next
End Sub

Цикл, который перемещается по каждой объединенной ячейке выделенного диапазона, каждый раз вызывает VBA код макроса для разъединения их диапазона объединения с учетом всех выше описанных условий.

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

Разделение файла на несколько частей(макрос)
Ребят кто-нить помогите. Я уже отчаялся не знаю как сделать это "Разделить файл на несколько частей.

Разделение чередующихся строчек в разные файлы
Условие: Файл с исходными данными содержит 2 стихотворения разных авторов (строки чередуются).

Запрос по наименованию, разделение таблицы на несколько запросов (таблиц)
Добрый день форумчане! Прошу помочь и подсказать как можно реализовать следующее: Есть Таблица.

Макрос для разделения таблицы на несколько
Добрый день! Имеется макрос (см.приложение) который разделяет одну большую таблицу по ключевому.

Решение

Bambaleilo, Попробуйте так. Тренироваться, естественно, на кошках (c)

Боже мой, pashulka, спасибо!)
Это какая-то магия!!)
Я вижу, как последовательно выбираются разные значения под фильтром)
Ииии. Я вижу нужные папки и файлы в папках!)))
То, что нужно! Великолепно просто!) Я в полном восторге!))

А в самом коде я поплыла на 5 строке))
(и мне очень понравилась третья! не догадалась раньше, буду пользоваться теперь) за это отдельное спасибо!))
Единственное, что ещё как-то зацепило моё сознание - это слово copy

Спасибо, pashulka, Вы мой спаситель-от-ручного-труда-избавитель!))

Решение

Bambaleilo, Если правильно понял, насчёт шаблона, то пример можно найти в аттаче. В качестве "шаблона" может выступать и самая обычная книга. Но обратите внимание на то, что даже там нет проверки корректности данных, которые мы используем в качестве источника для имени папки и файла. Так что, имеет смысл либо предварительно удалять весь мусор, либо проверять нет ли ошибки после сохранения книги.

Ооооо!)) Оооооо!)))
pashulka, Вы мой герой!)
Сегодня целый день работала с Вашим первым макросом)

Момент, когда я его запустила, мммм! Минута и 15 секунд на 12 тысяч строк! Шикааааарно!))
Я так вдохновилась, что нарисовала себе ещё один файлик-болванку, который бы брал данные из открытого файла (из примерно 200, которые сформировались) и делал то, что я делала обычно вручную (убирал дубликаты по столбцу, вставлял в таблицу, выделял строки на удаление) . Работала с ним сегодня цельный день, сделала 35 файлов даже с учётом двух макросов.

Если раньше по моим подсчётам у меня должно было уйти 14 дней (четырнадцааааать) , то сейчас я рассчитываю ещё примерно на 12 часов)
Боюсь представить, сколько будет, если использовать то, что Вы ещё написали!)) Пойду проверю!))
Спасибо-спасибо-спасибооооо!))

Предположим, у вас есть рабочий лист с огромными строками данных, и теперь вам нужно разбить данные на несколько рабочих листов на основе Имя столбец (см. следующий снимок экрана), а имена вводятся случайным образом. Может быть, вы можете сначала отсортировать их, а затем скопировать и вставить их один за другим в другие новые рабочие листы. Но для повторного копирования и вставки потребуется ваше терпение. Сегодня я расскажу о некоторых быстрых приемах решения этой задачи.

Разделить данные на несколько листов на основе столбца с кодом VBA

Если вы хотите быстро и автоматически разделить данные на основе значения столбца, следующий код VBA - хороший выбор. Пожалуйста, сделайте так:

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в окно модуля.

3, Затем нажмите F5 нажмите клавишу для запуска кода, и появится окно подсказки, напоминающее вам о выборе строки заголовка, см. снимок экрана:

4. А затем нажмите OK и во втором поле запроса выберите данные столбца, которые вы хотите разделить на основе, см. снимок экрана:

5, Затем нажмите OK, и все данные на активном листе разбиваются на несколько листов по значению столбца. И разделенные листы названы с именами разделенных ячеек. Смотрите скриншот:

Внимание: Разделенные листы помещаются в конец книги, где находится главный рабочий лист.

Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel

Как новичку в Excel, этот длинный код VBA несколько сложен для нас, и большинство из нас даже не знает, как изменить код по своему усмотрению. Здесь я представлю вам многофункциональный инструмент -Kutools for Excel, Его Разделить данные Утилита не только может помочь вам разделить данные на несколько листов на основе столбца, но также может разделить данные по количеству строк.

Примечание: Чтобы применить это Разделить данные, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, которые вы хотите разделить.

2. Нажмите Kutools Plus > Рабочий лист > Разделить данные, см. снимок экрана:

3. В Разделить данные на несколько листов диалоговое окно, вам необходимо:

1). Выбрать Конкретный столбец вариант в Сплит на основе раздел и выберите значение столбца, по которому вы хотите разделить данные, в раскрывающемся списке. (Если у ваших данных есть заголовки, и вы хотите вставить их в каждый новый разделенный лист, проверьте У моих данных есть заголовки опция).

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

3). Щелкните значок OK кнопка. Смотрите скриншот:

4. Теперь данные разделены на несколько листов в новой книге.

Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Загрузите бесплатную пробную версию прямо сейчас!

Читайте также: