Този урок ще ви покаже как да използвате PasteSpecial във VBA, за да поставите само определени свойства на клетката (напр. Стойности, формати)
В Excel, когато копирате и поставяте клетка, копирате и поставяте всички свойства на клетката: стойности, формати, формули, форматиране на числа, граници и т.н.
Вместо това можете да „Специално поставяне“, за да поставите само определени свойства на клетката. В Excel менюто Paste Special може да бъде достъпно с прекия път CTRL + ALT + V (след копиране на клетка):
Тук можете да видите всички комбинации от свойства на клетките, които можете да поставите.
Ако записвате макрос, докато използвате Специално меню за поставяне, можете просто да използвате генерирания код. Това често е най -лесният начин да използвате VBA за специално поставяне.
Поставяне на стойности
Вмъкване на стойности само поставя стойността на клетката. Ако клетката съдържа формула, Paste Values ще постави резултата от формулата.
Този код ще копира и постави стойности за една клетка на същия работен лист:
| 12 | Диапазон ("A1"). КопиранеДиапазон ("B1"). PasteSpecial Paste: = xlPasteValues |
Копиране и поставяне на стойност в различен лист
Този пример ще копира и постави стойности за единични клетки в различни работни листове
| 12 | Листове ("Sheet1"). Диапазон ("A1"). КопиранеЛистове ("Sheet2"). Диапазон ("B1"). PasteSpecial Paste: = xlPasteValues |
Тези примери ще копират и поставят стойности за диапазони от клетки:
Диапазони за копиране и поставяне на стойност
| 12 | Диапазон ("A1: B3"). КопиранеДиапазон ("C1"). PasteSpecial Paste: = xlPasteValues |
Копиране и колони за поставяне на стойност
| 12 | Колони ("А"). КопиранеКолони ("B"). PasteSpecial Paste: = xlPasteValues |
Редове за копиране и поставяне на стойност
| 12 | Редове (1). КопиранеРедове (2). PasteSpecial Paste: = xlPasteValues |
Поставяне на стойности и формати на числа
Поставянето на стойности ще постави само стойността на клетката. Не се поставя форматиране, включително форматиране на номера.
Често, когато поставяте стойности, вероятно ще искате да включите и форматирането на номера, така че стойностите ви да останат форматирани. Нека разгледаме един пример.
Тук ще оценим поставяне на клетка, съдържаща процент:
| 12 | Листове ("Лист1"). Колони ("D"). КопиранеЛистове ("Sheet2"). Колони ("B"). PasteSpecial Paste: = xlPasteValues |
Забележете как се губи форматирането на процентното число и вместо това се показва небрежна десетична стойност.
Вместо това нека използваме формати за поставяне на стойности и числа:
| 12 | Листове ("Лист1"). Колони ("D"). КопиранеЛистове ("Sheet2"). Колони ("B"). PasteSpecial Paste: = xlPasteValuesAndNumberFormats |
Сега можете да видите, че форматирането на числа също е поставено, поддържайки процентния формат.
.Цена вместо .Паста
Вместо да поставите стойности, можете да използвате свойството Value на обекта Range:
Това ще зададе стойността на клетката на A2 равна на стойността на клетката на B2
| 1 | Диапазон ("A2"). Стойност = Обхват ("B2"). Стойност |
Можете също така да зададете диапазон от клетки, равен на стойността на една клетка:
| 1 | Диапазон ("A2: C5"). Стойност = Обхват ("A1"). Стойност |
или диапазон от клетки, равен на друг диапазон от клетки с идентичен размер:
| 1 | Обхват ("B2: D4"). Стойност = Обхват ("A1: C3"). Стойност |
По -малко пишете, за да използвате свойството Value. Също така, ако искате да станете опитни с Excel VBA, трябва да сте запознати с работата със свойството Value на клетките.
Стойност на клетката срещу свойство Value2
Технически по -добре е да използвате свойството Value2 на клетка. Value2 е малко по -бърз (това има значение само при изключително големи изчисления) и свойството Value може да ви даде отсечен резултат, че клетката е форматирана като валута или дата. Въпреки това, 99%+ код, който съм виждал, използва .Value, а не .Value2. Аз лично не използвам .Value2, но трябва да сте наясно, че той съществува.
| 1 | Диапазон ("A2"). Стойност2 = Обхват ("B2"). Стойност2 |
Copy Paste Builder
Създадохме „Copy Paste Code Builder“, който улеснява генерирането на VBA код за копиране (или изрязване) и поставяне на клетки. Строителят е част от нашия Добавка VBA: AutoMacro.
AutoMacro съдържа и много други Генератори на кодове, обширен Библиотека с кодове, и мощен Инструменти за кодиране.
Специално поставяне - Формати и формули
Освен стойностите за поставяне, най -често срещаните опции за поставяне са формати за поставяне и формули за поставяне
Формати за поставяне
Форматите за поставяне ви позволява да поставите цялото форматиране на клетки.
| 12 | Диапазон ("A1: A10"). КопиранеДиапазон ("B1: B10"). PasteSpecial Paste: = xlPasteFormats |
Формули за поставяне
Поставете формули ще поставите само формулите на клетките. Това също е изключително полезно, ако искате да копирате формули на клетки, но не искате да копирате цветовете на фона на клетките (или друго форматиране на клетки).
| 12 | Диапазон ("A1: A10"). КопиранеДиапазон ("B1: B10"). PasteSpecial Paste: = xlPasteFormulas |
Поставяне на формули и формати на числа
Подобно на поставените стойности и формати на числа по -горе, можете също да копирате и поставите формати на числа заедно с формули
Тук ще копираме формула на клетка само с Форматиране на счетоводен номер и Формули за поставяне.
| 12 | Листове ("Лист1"). Диапазон ("D3"). КопиранеЛистове ("Sheet2"). Диапазон ("D3"). PasteSpecial xlPasteFormulas |
Забележете как се губи форматирането на числа и вместо това се показва небрежна незакръглена стойност.
Вместо това нека използваме Формати за поставяне и формати на числа:
| 12 | Листове ("Лист1"). Диапазон ("D3"). КопиранеЛистове ("Sheet2"). Диапазон ("D3"). PasteSpecial xlPasteFormulasAndNumberFormats |
Сега можете да видите, че форматирането на номера също е поставено, поддържайки формата на счетоводството.
Специално поставяне - Транспониране и пропускане на заготовки
Специална паста - Транспониране
Paste Special Transpose ви позволява да копирате и поставяте клетки, променящи ориентацията от горе-долу наляво-надясно (или обратно):
| 12 | Листове ("Sheet1"). Диапазон ("A1: A5"). КопиранеЛистове ("Sheet1"). Диапазон ("B1"). PasteSpecial Transpose: = Вярно |
Специално поставяне - Пропуснете празните места
Пропускането на заготовки е специална опция за поставяне, която изглежда не се използва толкова често, колкото би трябвало. Тя ви позволява да копирате само непразни клетки при копиране и поставяне. Така че празни клетки не се копират.
В този пример по -долу. Ще копираме колона А, правим обикновена паста в колона В и ще пропуснем празни полета в колона С. Можете да видите, че празните клетки не са поставени в колона С на изображението по -долу.
| 123 | Листове ("Sheet1"). Диапазон ("A1: A5"). КопиранеЛистове ("Sheet1"). Диапазон ("B1"). PasteSpecial SkipBlanks: = FalseЛистове ("Sheet1"). Диапазон ("C1"). PasteSpecial SkipBlanks: = Вярно |
Други специални опции за поставяне
Специално поставяне - Коментари
| 1 | Листове ("Sheet1"). Диапазон ("A1"). Копиране на листове ("Sheet1"). Диапазон ("E1"). PasteSpecial xlPasteComments |
Специална паста - Валидиране
| 12 | Листове ("Sheet1"). Диапазон ("A1: A4"). КопиранеЛистове ("Sheet1"). Диапазон ("B1: B4"). PasteSpecial xlPasteValidation |
Специално поставяне - Всички, използващи изходна тема
| 123 | Работни книги (1). Листове ("Лист1"). Диапазон ("A1: A2"). КопиранеРаботни книги (2). Листове ("Лист1"). Диапазон ("A1"). ПоставянеРаботни книги (2). Листове ("Sheet1"). Диапазон ("B1"). PasteSpecial xlPasteAllUsingSourceTheme |
Специално поставяне - Всички освен границите
| 123 | Диапазон ("B2: C3"). КопиранеДиапазон ("E2"). PasteSpecialДиапазон ("H2"). PasteSpecial xlPasteAllExceptBorders |
PasteSpecial - Ширини на колони
Моят личен фаворит. PasteSpecial Column Widths ще копира и постави ширината на колоните.
| 123 | Диапазон ("A1: A2"). КопиранеДиапазон ("C1"). PasteSpecialДиапазон ("E1"). PasteSpecial xlPasteColumnWidths |
PasteSpecial - Всички MergingConditionalFormats
| 123 | Диапазон ("A1: A4"). КопиранеДиапазон ("C1"). PasteSpecialДиапазон ("E1"). PasteSpecial xlPasteAllMergingConditionalFormats |
