Acest tutorial vă va arăta cum să utilizați PasteSpecial în VBA pentru a lipi numai anumite proprietăți ale celulei (ex. Valori, formate)
În Excel, când copiați și lipiți o celulă, copiați și lipiți toate proprietățile celulei: valori, formate, formule, formatarea numerelor, chenare etc.:
În schimb, puteți „Lipiți special” pentru a lipi numai anumite proprietăți ale celulei. În Excel, meniul Lipire specială poate fi accesat cu comanda rapidă CTRL + ALT + V (după copierea unei celule):
Aici puteți vedea toate combinațiile de proprietăți ale celulei pe care le puteți lipi.
Dacă înregistrați o macro în timp ce utilizați Meniul special Lipire, puteți utiliza pur și simplu codul generat. Acesta este adesea cel mai simplu mod de a utiliza VBA pentru a lipi special.
Inserați valori
Paste Values lipeste doar „valoarea” celulei. Dacă celula conținea o formulă, Paste Values va lipi rezultatul formulei.
Acest cod va copia și lipi valorile pentru o singură celulă din aceeași foaie de lucru:
| 12 | Gama („A1”). CopiațiRange ("B1"). PasteSpecial Paste: = xlPasteValues |
Copiați și lipiți valoarea pe o foaie diferită
Acest exemplu va copia și lipi valorile pentru celule unice pe foi de lucru diferite
| 12 | Foi („Sheet1”). Range („A1”). CopiațiFoi ("Sheet2"). Range ("B1"). PasteSpecial Paste: = xlPasteValues |
Aceste exemple vor copia și lipi valorile pentru o serie de celule:
Intervalele de copiere și lipire a valorilor
| 12 | Gama („A1: B3”). CopiațiRange ("C1"). PasteSpecial Paste: = xlPasteValues |
Copiați și coloanele de inserare a valorilor
| 12 | Coloane („A”). CopiațiColoane („B”). PasteSpecial Paste: = xlPasteValues |
Copiați și valorificați rândurile de lipire
| 12 | Rânduri (1) .CopieRows (2) .PasteSpecial Paste: = xlPasteValues |
Inserați valori și formate numerice
Inserarea valorilor va lipi doar valoarea celulei. Nu este lipită nicio formatare, inclusiv formatarea numerelor.
Adesea, atunci când lipiți valori, veți dori probabil să includeți și formatarea numărului, astfel încât valorile dvs. să rămână formatate. Să vedem un exemplu.
Aici vom valorifica lipirea unei celule care conține un procent:
| 12 | Foi („Foaie1”). Coloane („D”). CopiațiFoi ("Sheet2"). Coloane ("B"). PasteSpecial Paste: = xlPasteValues |
Observați cum se pierde formatarea numărului procentual și în schimb este afișată o valoare zecimală neglijentă.
În schimb, să folosim formatele de lipire valori și numere:
| 12 | Foi („Foaie1”). Coloane („D”). CopiațiFoi ("Sheet2"). Coloane ("B"). PasteSpecial Paste: = xlPasteValuesAndNumberFormats |
Acum puteți vedea și formatarea numărului este lipită, menținând formatul procentual.
.Valoare în loc de .Paste
În loc să lipiți valorile, puteți utiliza proprietatea Value a obiectului Range:
Aceasta va seta valoarea celulei A2 egală cu valoarea celulei B2
| 1 | Interval ("A2"). Valoare = Interval ("B2"). Valoare |
De asemenea, puteți seta un interval de celule egal cu valoarea unei singure celule:
| 1 | Interval ("A2: C5"). Valoare = Interval ("A1"). Valoare |
sau un interval de celule egal cu un alt interval de celule de dimensiuni identice:
| 1 | Interval ("B2: D4"). Valoare = Interval ("A1: C3"). Valoare |
Este mai puțin să tastați să utilizați proprietatea Value. De asemenea, dacă doriți să deveniți expert în Excel VBA, ar trebui să vă familiarizați cu lucrul cu proprietatea Value a celulelor.
Valoarea celulei vs. Proprietatea Value2
Din punct de vedere tehnic, este mai bine să utilizați proprietatea Value2 a unei celule. Valoarea2 este ușor mai rapidă (acest lucru contează doar cu calcule extrem de mari), iar proprietatea Valoare vă poate oferi un rezultat trunchiat al celulei formatate ca monedă sau dată. Cu toate acestea, 99% + din codul pe care l-am văzut folosește .Value și nu .Value2. Eu personal nu folosesc .Value2, dar ar trebui să știți că există.
| 1 | Interval ("A2"). Valoare2 = Interval ("B2"). Valoare2 |
Copiați Paste Builder
Am creat un „Copy Paste Code Builder” care facilitează generarea codului VBA pentru copiere (sau tăiere) și lipire a celulelor. Constructorul este parte a noastră Supliment VBA: AutoMacro.
AutoMacro conține și multe altele Generatoare de cod, o extinsă Biblioteca de coduri, și puternic Instrumente de codare.
Lipire specială - Formate și formule
Pe lângă valorile lipire, cele mai frecvente opțiuni lipire speciale sunt Formate lipire și Formule lipire
Lipiți formate
Formatele de lipire vă permit să lipiți toate formatările celulei.
| 12 | Gama („A1: A10”). CopiațiGama ("B1: B10"). PasteSpecial Paste: = xlPasteFormats |
Lipiți formule
Lipiți formulele va lipi doar formulele celulare. Acest lucru este, de asemenea, extrem de util dacă doriți să copiați formule de celule, dar nu doriți să copiați culorile de fundal ale celulei (sau alte formate de celule).
| 12 | Gama ("A1: A10"). CopiațiGama ("B1: B10"). PasteSpecial Paste: = xlPasteFormulas |
Lipiți formulele și formatele numerice
Similar cu Lipirea valorilor și formatelor numerice de mai sus, puteți copia și lipi formatele numerice împreună cu formulele
Aici vom copia o formulă de celulă numai cu formatarea numărului de contabilitate și formule de lipire.
| 12 | Foi ("Sheet1"). Range ("D3"). CopiațiFoi ("Sheet2"). Range ("D3"). PasteSpecial xlPasteFormulas |
Observați modul în care se pierde formatarea numerelor și în schimb este afișată o valoare neglijentă, fără rotunjire.
În schimb, să folosim formate de lipire formule și numere:
| 12 | Foi ("Sheet1"). Range ("D3"). CopiațiFoi ("Sheet2"). Range ("D3"). PasteSpecial xlPasteFormulasAndNumberFormats |
Acum puteți vedea și formatarea numărului este lipită, menținând formatul de contabilitate.
Lipire specială - Transpuneți și omiteți golurile
Lipire specială - Transpunere
Paste Special Transpose vă permite să copiați și să inserați celule schimbând orientarea de sus în jos în stânga-dreapta (sau vis-a-versa):
| 12 | Foi ("Sheet1"). Range ("A1: A5"). CopiațiFoi ("Sheet1"). Range ("B1"). PasteSpecial Transpose: = True |
Lipire specială - Omiteți golurile
Omiterea spațiilor goale este o opțiune specială de lipire care nu pare să fie folosită atât de des pe cât ar trebui. Vă permite să copiați numai celule care nu sunt goale atunci când copiați și lipiți. Deci celulele goale nu sunt copiate.
În acest exemplu de mai jos. Vom copia coloana A, vom efectua o lipire obișnuită în coloana B și vom omite lipirea în coloana C. Puteți vedea că celulele goale nu au fost lipite în coloana C din imaginea de mai jos.
| 123 | Foi ("Sheet1"). Range ("A1: A5"). CopiațiFoi de calcul („Foaie1”). Interval („B1”). PasteSpecial SkipBlanks: = FalseFoi de calcul („Sheet1”). Range („C1”). PasteSpecial SkipBlanks: = True |
Alte opțiuni speciale de lipire
Lipire specială - Comentarii
| 1 | Foi ("Foaie1"). Interval ("A1"). Copiați foi ("Foaie1"). Interval ("E1"). PasteSpecial xlPasteComments |
Lipire specială - validare
| 12 | Foi („Foaie1”). Interval („A1: A4”). CopiațiFoi ("Sheet1"). Range ("B1: B4"). PasteSpecial xlPasteValidation |
Lipire specială - Toate folosind tema sursă
| 123 | Cărți de lucru (1). Foi ("Sheet1"). Range ("A1: A2"). CopiereCărți de lucru (2). Foi ("Sheet1"). Range ("A1"). PasteSpecialCărți de lucru (2). Foi ("Sheet1"). Range ("B1"). PasteSpecial xlPasteAllUsingSourceTheme |
Lipire specială - Toate cu excepția frontierelor
| 123 | Gama („B2: C3”). CopiațiGama („E2”). PasteSpecialRange ("H2"). PasteSpecial xlPasteAllExceptBorders |
PasteSpecial - Lățimi ale coloanei
Un favorit personal al meu. PasteSpecial Column Widths va copia și lipi lățimea coloanelor.
| 123 | Gama („A1: A2”). CopiațiInterval („C1”). PasteSpecialGama ("E1"). PasteSpecial xlPasteColumnWidths |
PasteSpecial - All MergingConditionalFormats
| 123 | Gama („A1: A4”). CopiațiInterval („C1”). PasteSpecialGama („E1”). PasteSpecial xlPasteAllMergingConditionalFormats |
