Ten samouczek pokaże Ci, jak używać PasteSpecial w VBA do wklejania tylko niektórych właściwości komórek (np. wartości, formatów)
W programie Excel podczas kopiowania i wklejania komórki kopiujesz i wklejasz wszystkie właściwości komórki: wartości, formaty, formuły, formatowanie liczb, obramowania itp.:
Zamiast tego możesz "Wklej specjalnie", aby wkleić tylko niektóre właściwości komórki. W programie Excel do menu Wklej specjalnie można uzyskać dostęp za pomocą skrótu CTRL + ALT + V (po skopiowaniu komórki):
Tutaj możesz zobaczyć wszystkie kombinacje właściwości komórki, które możesz wkleić.
Jeśli nagrasz makro podczas korzystania z menu Wklej specjalnie, możesz po prostu użyć wygenerowanego kodu. Jest to często najłatwiejszy sposób użycia VBA do wklejenia specjalnego.
Wklej wartości
Wklej wartości wkleja tylko komórkę „wartość”. Jeśli komórka zawierała formułę, funkcja Wklej wartości spowoduje wklejenie wyniku formuły.
Ten kod skopiuje i wklei wartości dla pojedynczej komórki w tym samym arkuszu:
| 12 | Zakres ("A1").KopiujZakres("B1").PasteSpecial Paste:=xlPasteValues |
Kopiuj i wklej wartość do innego arkusza
Ten przykład skopiuje i wklei wartości dla pojedynczych komórek w różnych arkuszach roboczych
| 12 | Arkusze("Arkusz1").Zakres("A1").KopiujArkusze("Arkusz2").Range("B1").PasteSpecial Paste:=xlPasteValues |
Poniższe przykłady skopiują i wkleją wartości dla zakresów komórek:
Zakresy kopiowania i wklejania wartości
| 12 | Zakres ("A1:B3").KopiujZakres("C1").PasteSpecial Paste:=xlPasteValues |
Kolumny kopiowania i wklejania wartości
| 12 | Kolumny("A").KopiujColumns("B").PasteSpecial Paste:=xlPasteValues |
Kopiuj i wklejaj wartości
| 12 | Wiersze(1).KopiujRows(2).PasteSpecial Paste:=xlPasteValues |
Wklej wartości i formaty liczb
Wklejanie wartości spowoduje tylko wklejenie wartości komórki. Nie jest wklejane żadne formatowanie, w tym formatowanie liczb.
Często podczas wklejania wartości prawdopodobnie będziesz chciał uwzględnić również formatowanie liczb, aby Twoje wartości pozostały sformatowane. Spójrzmy na przykład.
Tutaj wartości wklejamy komórkę zawierającą procent:
| 12 | Arkusze("Arkusz1").Kolumny("D").KopiujArkusze("Arkusz2").Columns("B").PasteSpecial Paste:=xlPasteValues |
Zauważ, że formatowanie liczb procentowych zostało utracone, a zamiast tego wyświetlana jest niechlujna wartość dziesiętna.
Zamiast tego użyjmy formatów wklejania wartości i liczb:
| 12 | Arkusze("Arkusz1").Kolumny("D").KopiujSheets("Sheet2").Columns("B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats |
Teraz możesz zobaczyć, że formatowanie liczb jest również wklejane, zachowując format procentowy.
.Wartość zamiast .Wklej
Zamiast wklejania wartości możesz użyć właściwości Value obiektu Range:
To ustawi wartość komórki A2 równą wartości komórki B2
| 1 | Zakres("A2").Wartość = Zakres("B2").Wartość |
Możesz także ustawić zakres komórek równy wartości pojedynczej komórki:
| 1 | Zakres("A2:C5").Wartość = Zakres("A1").Wartość |
lub zakres komórek równy innemu zakresowi komórek o identycznej wielkości:
| 1 | Zakres("B2:D4").Wartość = Zakres("A1:C3").Wartość |
Korzystanie z właściwości Value to mniej pisania. Ponadto, jeśli chcesz biegle posługiwać się Excel VBA, powinieneś znać pracę z właściwością Value komórek.
Wartość komórki a właściwość Wartość2
Z technicznego punktu widzenia lepiej jest użyć właściwości Value2 komórki. Wartość2 jest nieco szybsza (ma to znaczenie tylko przy bardzo dużych obliczeniach), a właściwość Value może dać obcięty wynik, w którym komórka jest sformatowana jako waluta lub data. Jednak ponad 99% kodu, który widziałem, używa .Value, a nie .Value2. Osobiście nie używam .Value2, ale powinieneś mieć świadomość, że istnieje.
| 1 | Zakres("A2").Value2 = Zakres("B2").Value2 |
Kopiuj Wklej Konstruktor
Stworzyliśmy "Kopiuj wklej kod Builder", który ułatwia generowanie kodu VBA do kopiowania (lub wycinania) i wklejania komórek. Konstruktor jest częścią naszego Dodatek VBA: Automakro.
Automakro zawiera również wiele innych Generatory kodu, obszerny Biblioteka kodówi potężny Narzędzia do kodowania.
Wklej specjalnie - formaty i formuły
Oprócz wartości wklejania, najczęstszymi opcjami wklejania specjalnego są formaty wklejania i formuły wklejania
Wklej formaty
Formaty wklejania umożliwiają wklejenie całego formatowania komórek.
| 12 | Zakres ("A1:A10").KopiujZakres("B1:B10").PasteSpecial Paste:=xlPasteFormats |
Wklej formuły
Wklej formuły wkleją tylko formuły komórek. Jest to również niezwykle przydatne, jeśli chcesz kopiować formuły komórek, ale nie chcesz kopiować kolorów tła komórek (lub innego formatowania komórek).
| 12 | Zakres ("A1:A10").KopiujZakres("B1:B10").PasteSpecial Paste:=xlPasteFormules |
Wklej formuły i formaty liczb
Podobnie jak w przypadku wklejania wartości i formatów liczb powyżej, możesz również kopiować i wklejać formaty liczb wraz z formułami
Tutaj skopiujemy formułę komórki tylko z formatowaniem liczb księgowych i formułami wklejania.
| 12 | Arkusze("Arkusz1").Zakres("D3").KopiujArkusze("Arkusz2").Range("D3").PasteSpecial xlPasteFormules |
Zauważ, że formatowanie liczb jest tracone, a zamiast tego wyświetlana jest niechlujna niezaokrąglona wartość.
Zamiast tego użyjmy formatów wklejania formuł i liczb:
| 12 | Arkusze("Arkusz1").Zakres("D3").KopiujArkusze("Arkusz2").Range("D3").PasteSpecial xlPasteFormulasAndNumberFormats |
Teraz możesz zobaczyć, że formatowanie liczb jest również wklejone, zachowując format Księgowość.
Wklej specjalnie - transpozycja i pomijanie pustych miejsc
Wklej specjalnie - Transpozycja
Wklej specjalnie Transpozycja umożliwia kopiowanie i wklejanie komórek zmieniając orientację z góry na dół na lewo-prawo (lub odwrotnie):
| 12 | Arkusze("Arkusz1").Zakres("A1:A5").KopiujArkusze("Arkusz1").Range("B1").PasteSpecial Transpozycja:=True |
Wklej specjalnie - Pomiń puste miejsca
Pomiń puste miejsca to specjalna opcja wklejania, która nie wydaje się być używana tak często, jak powinna. Pozwala kopiować tylko niepuste komórki podczas kopiowania i wklejania. Więc puste komórki nie są kopiowane.
W tym przykładzie poniżej. Skopiujemy kolumnę A, zrobimy zwykłe wklejanie w kolumnie B i pominiemy puste wklejanie w kolumnie C. Możesz zobaczyć, że puste komórki nie zostały wklejone do kolumny C na poniższym obrazku.
| 123 | Arkusze("Arkusz1").Zakres("A1:A5").KopiujArkusze("Arkusz1").Range("B1").PasteSpecial SkipBlanks:=FalseArkusze("Arkusz1").Range("C1").PasteSpecial SkipBlanks:=True |
Inne opcje specjalne wklejania
Wklej specjalnie - Komentarze
| 1 | Arkusze("Arkusz1").Range("A1").Copy Sheets("Arkusz1").Range("E1").PasteSpecial xlPasteComments |
Wklej specjalnie - walidacja
| 12 | Arkusze("Arkusz1").Zakres("A1:A4").KopiujArkusze("Arkusz1").Range("B1:B4").PasteSpecial xlPasteValidation |
Wklej specjalnie - wszystko przy użyciu motywu źródłowego
| 123 | Skoroszyty(1).Sheets("Arkusz1").Range("A1:A2").CopySkoroszyty(2).Sheets("Arkusz1").Range("A1").PasteSpecialSkoroszyty(2).Sheets("Arkusz1").Range("B1").PasteSpecial xlPasteAllUsingSourceTheme |
Wklej specjalnie - wszystko oprócz obramowań
| 123 | Zakres("B2:C3").KopiujZakres ("E2").PasteSpecialZakres("H2").PasteSpecial xlPasteAllExceptBorders |
PasteSpecial - Szerokości kolumn
Mój osobisty faworyt. PasteSpecial Column Widths skopiuje i wklei szerokość kolumn.
| 123 | Zakres ("A1:A2").KopiujZakres("C1").PasteSpecialZakres("E1").PasteSpecial xlPasteColumnWidths |
WklejSpecjalnie - Wszystkie scalające formaty warunkowe
| 123 | Zakres ("A1:A4").KopiujZakres("C1").PasteSpecialZakres ("E1") |
