Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » met.constr
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Копирование непустых данных в умную таблицу
 
#
03.08.2019 16:13:01
Юрий М, Спасибо, согласен так будет лучше выглядеть!  
 
Перейти    
Копирование непустых данных в умную таблицу
 
#
02.08.2019 16:38:47
Цитата
Андрей Лящук написал:

Код
Sub Copy_paste_test()
    Dim lRow&, arr0, arr1
    With Sheets("Таблица исходная")
        lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        If lRow < 15 Then Exit Sub
        arr0 = Array(.[C6], .[C5], .[C8], .[C7])
        arr1 = .[A15].Resize(lRow - 15 + 1, 14).Value
    End With
    With [ТаблицаЗначений].ListObject.Range
        With .Offset(.Rows.Count).Resize(lRow - 15 + 1)
            .Resize(, 4) = arr0
            .Offset(, 4).Resize(, 14) = arr1
        End With
    End With
    Erase arr0, arr1
End Sub
Андрей, добрый день!
Кажется это как раз то что нужно, а могли бы вы подсказать что именно и как работает в макросе!?  
Изменено: met.constr - 03.08.2019 16:12:24
 
Перейти    
Копирование непустых данных в умную таблицу
 
#
01.08.2019 21:40:25
Уважаемые господа ПланетыЭксель.
Я начинающий специалист в части VBA и пытаюсь решить необходимые мне задачи с его помощью.

Задача стояла из исходной таблицы копировать "не пустые" значения в базу чтобы потом можно было из базы запрашивать кому какие наценки и какая себестоимость продукции была просчитана.

За "не пустые" значения я принял количество изделий. Если оно не равно 0 в таблице то строку считаем не пустой.

Свое решение пишу ниже (код) но у меня получилось очень громоздко и долго обрабатывается макрос после нажатия кнопки.
Хотел использовать диапазон range но я не знаю как его можно поместить в конкретные столбцы "умной таблицы".
Учитывая что в исходной таблице будет не 3 а порядка 20-30 строк, код получится очень большим и его обработка займет очень много времени.

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

Файл с примером тоже вкладываю.

Это в продолжении предыдущей темы: Копирование не пустых значений и вставка в другую табицу

Ответ я по ней получил, но разъяснений что откуда берется и что происходит не смог получить, потому сваял так как мог.
не судите строго.


Сам макрос кода по копированию.
Код
Sub Copy_paste_test()
    'В этом макросе нужно:
    '1) определить не пустой диапозон ячеек с товарами, тоесть в дипозон должен быть выбран товар
    '2) скопировать данный диапозон по ячейкам и вставить его в нужные ячейки базы
    Application.ScreenUpdating = False 'отключаем обновление экрана
    Dim forma As Worksheet, baza As Worksheet, smart As ListObject, lngI As Long 'назначаем переменные
    Set forma = Worksheets("Таблица исходная"):
    Set baza = Worksheets("Таблица для вставки"):
    Set smart = baza.ListObjects("ТаблицаЗначений"):
    If forma.Range("C15") > 0 Then 'проверяем ячейку в таблице на наличие значения, если значение в столбце количества больше 0 то вставляем всю строку в базу
    smart.ListRows.Add 'добавляем в "умную" таблицу строку ? без новой строки добавлять значения будет некуда.
    lngI = smart.ListRows.Count 'считаем количество строк с данными в "умной" таблице ? зачем считаем не понятно, но без подсчета не работает.
    forma.[C6].Copy: smart.DataBodyRange(lngI, 1).PasteSpecial Paste:=xlPasteValues 'ID клиента
    forma.[C5].Copy: smart.DataBodyRange(lngI, 2).PasteSpecial Paste:=xlPasteValues 'клиент
    forma.[C8].Copy: smart.DataBodyRange(lngI, 3).PasteSpecial Paste:=xlPasteValues 'id заказа
    forma.[C7].Copy: smart.DataBodyRange(lngI, 4).PasteSpecial Paste:=xlPasteValues '№ заказа
    'дальше идут данные из таблицы с товаром
    forma.[A15].Copy: smart.DataBodyRange(lngI, 5).PasteSpecial Paste:=xlPasteValues 'id номенклатуры
    forma.[B15].Copy: smart.DataBodyRange(lngI, 6).PasteSpecial Paste:=xlPasteValues 'наименование детали по заказу
    forma.[C15].Copy: smart.DataBodyRange(lngI, 7).PasteSpecial Paste:=xlPasteValues 'кол-во шт
    forma.[D15].Copy: smart.DataBodyRange(lngI, 8).PasteSpecial Paste:=xlPasteValues 'ед.изм
    forma.[E15].Copy: smart.DataBodyRange(lngI, 9).PasteSpecial Paste:=xlPasteValues 'вес 1 шт кг
    forma.[F15].Copy: smart.DataBodyRange(lngI, 10).PasteSpecial Paste:=xlPasteValues 'общий вес по позиции
    forma.[G15].Copy: smart.DataBodyRange(lngI, 11).PasteSpecial Paste:=xlPasteValues 'стоимость стали сумма
    forma.[H15].Copy: smart.DataBodyRange(lngI, 12).PasteSpecial Paste:=xlPasteValues 'Стоимость работ Сумма
    forma.[I15].Copy: smart.DataBodyRange(lngI, 13).PasteSpecial Paste:=xlPasteValues 'Общая себестоимость заказа
    forma.[J15].Copy: smart.DataBodyRange(lngI, 14).PasteSpecial Paste:=xlPasteValues 'цена за 1шт
    forma.[K15].Copy: smart.DataBodyRange(lngI, 15).PasteSpecial Paste:=xlPasteValues 'наценка РСК
    forma.[L15].Copy: smart.DataBodyRange(lngI, 16).PasteSpecial Paste:=xlPasteValues 'сумма наценки РСК
    forma.[M15].Copy: smart.DataBodyRange(lngI, 17).PasteSpecial Paste:=xlPasteValues 'сумма с наценкой
    forma.[N15].Copy: smart.DataBodyRange(lngI, 18).PasteSpecial Paste:=xlPasteValues 'цена за шт РСК
    End If
    If forma.Range("C16") > 0 Then 'проверяем ячейку в таблице на наличие значения, если значение в столбце количества больше 0 то вставляем всю строку в базу
    smart.ListRows.Add 'добавляем в "умную" таблицу строку ? без новой строки добавлять значения будет некуда.
    lngI = smart.ListRows.Count 'считаем количество строк с данными в "умной" таблице ? зачем считаем не понятно, но без подсчета не работает.
    forma.[C6].Copy: smart.DataBodyRange(lngI, 1).PasteSpecial Paste:=xlPasteValues 'ID клиента
    forma.[C5].Copy: smart.DataBodyRange(lngI, 2).PasteSpecial Paste:=xlPasteValues 'клиент
    forma.[C8].Copy: smart.DataBodyRange(lngI, 3).PasteSpecial Paste:=xlPasteValues 'id заказа
    forma.[C7].Copy: smart.DataBodyRange(lngI, 4).PasteSpecial Paste:=xlPasteValues '№ заказа
    'дальше идут данные из таблицы с товаром
    forma.[A16].Copy: smart.DataBodyRange(lngI, 5).PasteSpecial Paste:=xlPasteValues 'id номенклатуры
    forma.[B16].Copy: smart.DataBodyRange(lngI, 6).PasteSpecial Paste:=xlPasteValues 'наименование детали по заказу
    forma.[C16].Copy: smart.DataBodyRange(lngI, 7).PasteSpecial Paste:=xlPasteValues 'кол-во шт
    forma.[D16].Copy: smart.DataBodyRange(lngI, 8).PasteSpecial Paste:=xlPasteValues 'ед.изм
    forma.[E16].Copy: smart.DataBodyRange(lngI, 9).PasteSpecial Paste:=xlPasteValues 'вес 1 шт кг
    forma.[F16].Copy: smart.DataBodyRange(lngI, 10).PasteSpecial Paste:=xlPasteValues 'общий вес по позиции
    forma.[G16].Copy: smart.DataBodyRange(lngI, 11).PasteSpecial Paste:=xlPasteValues 'стоимость стали сумма
    forma.[H16].Copy: smart.DataBodyRange(lngI, 12).PasteSpecial Paste:=xlPasteValues 'Стоимость работ Сумма
    forma.[I16].Copy: smart.DataBodyRange(lngI, 13).PasteSpecial Paste:=xlPasteValues 'Общая себестоимость заказа
    forma.[J16].Copy: smart.DataBodyRange(lngI, 14).PasteSpecial Paste:=xlPasteValues 'цена за 1шт
    forma.[K16].Copy: smart.DataBodyRange(lngI, 15).PasteSpecial Paste:=xlPasteValues 'наценка РСК
    forma.[L16].Copy: smart.DataBodyRange(lngI, 16).PasteSpecial Paste:=xlPasteValues 'сумма наценки РСК
    forma.[M16].Copy: smart.DataBodyRange(lngI, 17).PasteSpecial Paste:=xlPasteValues 'сумма с наценкой
    forma.[N16].Copy: smart.DataBodyRange(lngI, 18).PasteSpecial Paste:=xlPasteValues 'цена за шт РСК
    End If
    If forma.Range("C17") > 0 Then 'проверяем ячейку в таблице на наличие значения, если значение в столбце количества больше 0 то вставляем всю строку в базу
    smart.ListRows.Add 'добавляем в "умную" таблицу строку ? без новой строки добавлять значения будет некуда.
    lngI = smart.ListRows.Count 'считаем количество строк с данными в "умной" таблице ? зачем считаем не понятно, но без подсчета не работает.
    forma.[C6].Copy: smart.DataBodyRange(lngI, 1).PasteSpecial Paste:=xlPasteValues 'ID клиента
    forma.[C5].Copy: smart.DataBodyRange(lngI, 2).PasteSpecial Paste:=xlPasteValues 'клиент
    forma.[C8].Copy: smart.DataBodyRange(lngI, 3).PasteSpecial Paste:=xlPasteValues 'id заказа
    forma.[C7].Copy: smart.DataBodyRange(lngI, 4).PasteSpecial Paste:=xlPasteValues '№ заказа
    'дальше идут данные из таблицы с товаром
    forma.[A17].Copy: smart.DataBodyRange(lngI, 5).PasteSpecial Paste:=xlPasteValues 'id номенклатуры
    forma.[B17].Copy: smart.DataBodyRange(lngI, 6).PasteSpecial Paste:=xlPasteValues 'наименование детали по заказу
    forma.[C17].Copy: smart.DataBodyRange(lngI, 7).PasteSpecial Paste:=xlPasteValues 'кол-во шт
    forma.[D17].Copy: smart.DataBodyRange(lngI, 8).PasteSpecial Paste:=xlPasteValues 'ед.изм
    forma.[E17].Copy: smart.DataBodyRange(lngI, 9).PasteSpecial Paste:=xlPasteValues 'вес 1 шт кг
    forma.[F17].Copy: smart.DataBodyRange(lngI, 10).PasteSpecial Paste:=xlPasteValues 'общий вес по позиции
    forma.[G17].Copy: smart.DataBodyRange(lngI, 11).PasteSpecial Paste:=xlPasteValues 'стоимость стали сумма
    forma.[H17].Copy: smart.DataBodyRange(lngI, 12).PasteSpecial Paste:=xlPasteValues 'Стоимость работ Сумма
    forma.[I17].Copy: smart.DataBodyRange(lngI, 13).PasteSpecial Paste:=xlPasteValues 'Общая себестоимость заказа
    forma.[J17].Copy: smart.DataBodyRange(lngI, 14).PasteSpecial Paste:=xlPasteValues 'цена за 1шт
    forma.[K17].Copy: smart.DataBodyRange(lngI, 15).PasteSpecial Paste:=xlPasteValues 'наценка РСК
    forma.[L17].Copy: smart.DataBodyRange(lngI, 16).PasteSpecial Paste:=xlPasteValues 'сумма наценки РСК
    forma.[M17].Copy: smart.DataBodyRange(lngI, 17).PasteSpecial Paste:=xlPasteValues 'сумма с наценкой
    forma.[N17].Copy: smart.DataBodyRange(lngI, 18).PasteSpecial Paste:=xlPasteValues 'цена за шт РСК
    End If

     ActiveWorkbook.Save
    Application.CutCopyMode = False
    Application.ScreenUpdating = True 'включаем обратно обновление экрана.
End Sub
 
Перейти    
Копирование не пустых значений и вставка в другую табицу, Необходимо из 1 таблицы скопировать только не пустые и вставить их в другую таблицы
 
#
21.07.2019 20:42:57
Андрей VG, добрый вечер!
В принципе вариант рабочий. Единственное что если в исходную таблицу товары добавлять  через строку, а не подряд, то потом и пустые строки добавляются.

Цитата
Андрей VG написал:
ОБъясните, пожалуйста,Цитататолько не пустые
Касательно только не пустых, я подразумевал что ячейки в диапозоне по первой строке например А2:H2 должны быть заполнены значениями, это мы считаем не пустым значением, а если исходная таблица содержит заполненные А2:H2 то соотвественно остальные A3:H15 мы считаем пустыми.

Цитата
Андрей VG написал:
зачем это требование? Если вы добавляете данные в таблицу
Как новые строки необходимо добавлять так как не должно быть наложений, тоесть если мы добавляем новые значения, они не должны в таблицу для вставки записаться на уже имеющиеся значения.

Как писал выше, вариант в принципе рабочий, но мне его нужно связать с другими своими строками макроса, могли бы вы Андрей VG, более подробно рассказать что и как действует в макросе или дать комментарий к коду?? Я был бы весьма признателен. Так как не силен в написании макросов.


Код
Public Sub addNewRowsFromTable()
    Dim whishodniy As Worksheet, whBD As Worksheet, lobjDB As ListObject, lngI As Long
    Dim arrData
    Set whishodniy = Worksheets("Таблица исходная"): Set whBD = Worksheets("Таблица для вставки"): Set lobjDB = whBD.ListObjects("ТаблицаЗначений")
    lngI = whishodniy.Cells(whishodniy.Rows.Count, 1).End(xlUp).Row
    arrData = whishodniy.Range(whishodniy.Cells(2, whishodniy.UsedRange.Column), whishodniy.Cells(lngI, whishodniy.UsedRange.Columns.Count + whishodniy.UsedRange.Column - 1)).Value
    If lobjDB.DataBodyRange Is Nothing Then
        lobjDB.HeaderRowRange(1).Offset(1, 0).Resize(UBound(arrData, 1), UBound(arrData, 2)).Value = arrData
    Else
        lobjDB.DataBodyRange.Cells(lobjDB.DataBodyRange.Rows.Count, 1).Offset(1, 0).Resize(UBound(arrData, 1), UBound(arrData, 2)).Value = arrData
    End If
End Sub

За ранее спасибо!  
Изменено: met.constr - 21.07.2019 20:44:00
 
Перейти    
Копирование не пустых значений и вставка в другую табицу, Необходимо из 1 таблицы скопировать только не пустые и вставить их в другую таблицы
 
#
21.07.2019 14:05:25
Добрый день Уважаемые знатоки, долго искал подходящий ответ на вопрос на форуме, но не нашел в силу того что не очень силен в макросах, но стараюсь разобраться.
Сейчас стоит следующая задача, необходимо из таблицы А2:H15 (лист: Таблица исходная) скопировать не пустые значения и потом добавить их как новые строки ниже в "Умную таблицу" (ТаблицаЗначений) на листе: Таблица для вставки. Вставлять данные необходимо не как формулы а как простое значение ячейки.

Как копировать просто значение по сроке и потом переносить их в умную таблицу я сделал, но вот задача в том что в таблице А2:H15 (лист: Таблица исходная)  может быть как 1 так и 5 и 12 и 20 значений.
В файл примера добавил небольшой макрос который сейчас у меня работает, но только по первой строке, а как сделать его так чтобы можно было копировать не пустые значения и сразу вставлять их все по нажатию кнопки я не  знаю.

Помогите пожалуйста решить эту задачу.
Ниже макрос который присутствует в файле (для наглядности).
Код
Sub Копирование_первой_строки()
Dim whishodniy As Worksheet, whBD As Worksheet, lobjDB As ListObject, lngI As Long
    Application.ScreenUpdating = False 'отключаем обновление экрана
    Set whishodniy = Worksheets("Таблица исходная"): Set whBD = Worksheets("Таблица для вставки"): Set lobjDB = whBD.ListObjects("ТаблицаЗначений")
    lobjDB.ListRows.Add 'добавляем в "умную" таблицу строку
    lngI = lobjDB.ListRows.Count 'считаем количество строк с данными в "умной" таблице
    'копируем и переносим как спец.вставка значений по каждой ячейке таблицы из формы с листа таблица исходная в умную таблицу листа для вставки
    whishodniy.[A2].Copy: lobjDB.DataBodyRange(lngI, 1).PasteSpecial Paste:=xlPasteValues
    whishodniy.[B2].Copy: lobjDB.DataBodyRange(lngI, 2).PasteSpecial Paste:=xlPasteValues
    whishodniy.[C2].Copy: lobjDB.DataBodyRange(lngI, 3).PasteSpecial Paste:=xlPasteValues
    whishodniy.[D2].Copy: lobjDB.DataBodyRange(lngI, 4).PasteSpecial Paste:=xlPasteValues
    whishodniy.[E2].Copy: lobjDB.DataBodyRange(lngI, 5).PasteSpecial Paste:=xlPasteValues
    whishodniy.[F2].Copy: lobjDB.DataBodyRange(lngI, 6).PasteSpecial Paste:=xlPasteValues
    whishodniy.[G2].Copy: lobjDB.DataBodyRange(lngI, 7).PasteSpecial Paste:=xlPasteValues
    whishodniy.[H2].Copy: lobjDB.DataBodyRange(lngI, 8).PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.Save 'сохраняем результат чтобы ничего не потерять
    Application.CutCopyMode = False
    Application.ScreenUpdating = True 'включаем обратно обновление экрана.
End Sub
Очень рассчитываю на помощь присутствующих!  
 
Перейти    
Зависимые выпадающие списки: убрать лишнее при выборе номенклатур
 
#
11.12.2018 09:26:11
gling,Спасибо, буду искать подходящую модель группировки данных для того чтобы оптимизировать данный процесс. Может и правда макросы...
Если будет решение, отпишусь.

Еще раз спасибо за совет!  
 
Перейти    
Зависимые выпадающие списки: убрать лишнее при выборе номенклатур
 
#
10.12.2018 13:14:48
Цитата
met.constr написал:
Просьба только дать комментарий, как это все работает (в кратце).
gling, кажется разобрался, через Индекс (поискпоз) в самом именованном диапозоне!
А возможно ли применить аналогичный алгоритм для справочника в одной таблице? (прилагаю сам справочник металла в сообщение)? или в таком случае работать не будет?

Так как писал выше таких справочников будет несколько, и они будут дополнятся. В плане таблицы (Справочник металла (как он есть)) будет проще организовать добавление строк в справочник, но ожно ли будет организовать аналогичный алгоритм работы и связки формулы через именованый диапозон Индекс(поискпоз)???  
 
Перейти    
Зависимые выпадающие списки: убрать лишнее при выборе номенклатур
 
#
10.12.2018 13:05:43
gling, Спасибо за ответ! Так намного лучше!!!!

Однозначно лучше чем было с точки зрения организации файла в комплексе.
Просьба только дать комментарий, как это все работает (в кратце).
Я понимаю что через данные/проверка данных/список, - организованы выпадающие списки, не понимаю как организовывается взаимосвязь между А2 и В2. тоесть пи выборе определенного "вида проката" подвид подтягивается в соответствии с "видом"
Буду очень признателен за комментарий.!  
 
Перейти    
Зависимые выпадающие списки: убрать лишнее при выборе номенклатур
 
#
07.12.2018 18:37:21
В том числе и там был, но не подходит!  Хотя полезные приемы почерпнул.
 
Перейти    
Зависимые выпадающие списки: убрать лишнее при выборе номенклатур
 
#
07.12.2018 18:23:50
Добрый вечер экспертам!
В экселе не профессионал но стараюсь решать задачи теми методами которые мне подсилу.
Сейчас стоит задача оптимизировать работу со справочниками металлопроката.
В справочнике есть категории:
- вид проката
- подвид проката
- Нормативно-технический документ
- типоразмер
Это 4 параметра которые должны быть организованны в виде списков.

Решил задачу через именованые списки и таблицу соответствий (долго с ней парился, но довел до ума)

Вопрос к Вам ЗНАТОКИ!
Исходя из того что есть во вложении возможно ли решить данную задачу "более изящно" или может с помощью неизвестных мне формул и их связок вообще все просто можно сделать!?

Буду рад советам.
На данный момент за неимением лучшего, оставил так, но продолжаю поиски оптимального варианта.
 
Перейти    
Копирование данных из одной умной таблицы и вставка в другую на новом листе.
 
#
27.11.2018 15:47:17
Цитата
kalmar написал:
а макрос точно нужен? Вам никто сейчас процедуры писать не будет через заполнения если они у Вас полностью совпадать будут, то пишите во второй таблице формулы
Тут пробелма в том, что таблица 1 служит как форма для ввода данных. После того как все заполнено, макрос передал данные в БД (лист 2) мы из таблицы 1 данные удаляем и вводим новые. Таким образом в листе 2 БД появится база со всеми данными внесенными через таблицу 1.
ДВССЫЛ и так использую но в этом случае он не подойдет так как данные не будут статичны. нужно именно копировать значения ячеек и всатвлять как значения

Цитата
...или просто (без умной таблицы) Код ? 1='1'!A2
вот тут не совсем понял?  
 
Перейти    
Копирование данных из одной умной таблицы и вставка в другую на новом листе.
 
#
27.11.2018 15:24:28
Добрый день уважаемы форумчане. Есть задача в реализации одного проекта для себя.
Хочется построить некий инструмент оптимизации (автоматизации) скажем так своими силами.
Сейчас столкнулся с задачами которые можно решить через макрос.
Копал форум, интернет по теме VBA но ответа на свой вопрос не нашел.
описание задачи примерно следующее:
Есть лист 1  на котором расположена "умная таблица"  (specifikaciya) нужно брать и все данные этой таблицы копировать на лист 2 в другую умную таблицу (BD_zakazi)
Суть в том что таблица на листе 1 будет как некая форма ввода информации, и после ввода информации даныне должны попадать в базу (BD_zakazi)
Пробовал сам через запись макроса но получается коряво, так как (BD_zakazi) должна дополнятся вниз, тоесть при наличии в (BD_zakazi) 10 заполненных строк остальные записи должны перемещаться ниже.

Также прошу совета, подскажите какой нибудь видео курс по VBA чтобы понять принципы и хотябы отчасти разобраться в написании простых макросов типа копи-паст  

За ранее признателен за помощь!
 
Перейти    
Страницы: 1
Наверх