Questo tutorial ti mostrerà come utilizzare PasteSpecial in VBA per incollare solo determinate proprietà della cella (es. valori, formati)
In Excel, quando copi e incolli una cella, copi e incolli tutte le proprietà della cella: valori, formati, formule, formattazione numerica, bordi e così via:
Invece, puoi "Incolla speciale" per incollare solo determinate proprietà della cella. In Excel, è possibile accedere al menu Incolla speciale con la scorciatoia CTRL + ALT + V (dopo aver copiato una cella):
Qui puoi vedere tutte le combinazioni di proprietà delle celle che puoi incollare.
Se registri una macro mentre utilizzi il menu Incolla speciale, puoi semplicemente utilizzare il codice generato. Questo è spesso il modo più semplice per utilizzare VBA per incollare speciale.
Incolla valori
Incolla valori incolla solo il "valore" della cella. Se la cella conteneva una formula, Incolla valori incollerà il risultato della formula.
Questo codice copierà e incollerà i valori per una singola cella nello stesso foglio di lavoro:
| 12 | Intervallo ("A1"). CopiaRange("B1").PasteSpecial Paste:=xlPasteValues |
Copia e incolla valore su fogli diversi
Questo esempio copia e incolla i valori per singole celle su fogli di lavoro diversi
| 12 | Fogli("Foglio1").Intervallo("A1").CopiaFogli("Foglio2").Range("B1").PasteSpecial Paste:=xlPasteValues |
Questi esempi copiano e incollano i valori per un intervallo di celle:
Intervalli di copia e incolla di valori
| 12 | Intervallo ("A1: B3"). CopiaRange("C1").PasteSpecial Paste:=xlPasteValues |
Copia e incolla le colonne dei valori
| 12 | Colonne ("A"). CopiaColonne("B").PasteSpecial Paste:=xlPasteValues |
Copia e incolla righe di valore
| 12 | Righe(1).CopiaRighe(2).PasteSpecial Paste:=xlPasteValues |
Incolla valori e formati numerici
Incollare valori incollerà solo il valore della cella. Nessuna formattazione viene incollata, inclusa la formattazione dei numeri.
Spesso quando incolli i valori probabilmente vorrai includere anche la formattazione del numero in modo che i tuoi valori rimangano formattati. Diamo un'occhiata a un esempio.
Qui valuteremo incollare una cella contenente una percentuale:
| 12 | Fogli("Foglio1").Colonne("D").CopiaFogli("Foglio2").Columns("B").PasteSpecial Paste:=xlPasteValues |
Nota come la formattazione del numero percentuale viene persa e viene invece mostrato un valore decimale sciatto.
Usiamo invece i formati Incolla valori e numeri:
| 12 | Fogli("Foglio1").Colonne("D").CopiaFogli("Foglio2").Columns("B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats |
Ora puoi vedere che anche la formattazione del numero è incollata, mantenendo il formato percentuale.
.Valore invece di .Incolla
Invece di incollare valori, puoi usare la proprietà Value dell'oggetto Range:
Questo imposterà il valore della cella di A2 uguale al valore della cella di B2
| 1 | Intervallo ("A2"). Valore = Intervallo ("B2"). Valore |
Puoi anche impostare un intervallo di celle uguale al valore di una singola cella:
| 1 | Intervallo ("A2: C5"). Valore = Intervallo ("A1"). Valore |
o un intervallo di celle uguale a un altro intervallo di celle di dimensioni identiche:
| 1 | Intervallo ("B2: D4"). Valore = Intervallo ("A1: C3"). Valore |
È meno necessario digitare per utilizzare la proprietà Value. Inoltre, se vuoi diventare esperto con Excel VBA, dovresti avere familiarità con l'utilizzo della proprietà Value delle celle.
Valore cella vs. Proprietà Value2
Tecnicamente, è meglio usare la proprietà Value2 di una cella. Value2 è leggermente più veloce (questo è importante solo con calcoli estremamente grandi) e la proprietà Value potrebbe fornire un risultato troncato della cella formattata come valuta o data. Tuttavia, il 99%+ del codice che ho visto utilizza .Value e non .Value2. Personalmente non uso .Value2, ma dovresti essere consapevole che esiste.
| 1 | Intervallo("A2").Valore2 = Intervallo("B2").Valore2 |
Copia Incolla Builder
Abbiamo creato un "Copy Paste Code Builder" che semplifica la generazione di codice VBA per copiare (o tagliare) e incollare celle. Il costruttore fa parte del nostro Componente aggiuntivo VBA: AutoMacro.
AutoMacro contiene anche molti altri Generatori di codice, un vasto Libreria di codici, e potente Strumenti di codifica.
Incolla speciale - Formati e formule
Oltre a Incolla valori, le opzioni Incolla speciale più comuni sono Incolla formati e Incolla formule
Incolla formati
Incolla formati ti consente di incollare tutta la formattazione delle celle.
| 12 | Intervallo ("A1: A10"). CopiaRange("B1:B10").PasteSpecial Paste:=xlPasteFormats |
Incolla formule
Incolla formule incollerà solo le formule delle celle. Questo è anche estremamente utile se vuoi copiare le formule delle celle, ma non vuoi copiare i colori di sfondo delle celle (o altra formattazione delle celle).
| 12 | Intervallo ("A1: A10"). CopiaRange("B1:B10").PasteSpecial Paste:=xlPasteFormulas |
Incolla formule e formati numerici
Simile a Incolla valori e formati numerici sopra, puoi anche copiare e incollare formati numerici insieme alle formule
Qui copieremo una formula di cella solo con la formattazione del numero di contabilità e Incolla le formule.
| 12 | Fogli("Foglio1").Intervallo("D3").CopiaFogli("Foglio2").Intervallo("D3").PasteSpecial xlPasteFormulas |
Nota come la formattazione del numero viene persa e viene invece mostrato un valore approssimativo non arrotondato.
Usiamo invece i formati Incolla formule e numeri:
| 12 | Fogli("Foglio1").Intervallo("D3").CopiaFogli("Foglio2").Intervallo("D3").IncollaSpecial xlIncollaFormuleENumeroFormati |
Ora puoi vedere che anche la formattazione del numero è incollata, mantenendo il formato Contabilità.
Incolla speciale - Trasponi e salta spazi vuoti
Incolla speciale - Trasponi
Paste Special Transpose ti consente di copiare e incollare le celle cambiando l'orientamento da alto-basso a sinistra-destra (o viceversa):
| 12 | Fogli("Foglio1").Intervallo("A1:A5").CopiaFogli("Foglio1").Intervallo("B1").PasteSpecial Transpose:=True |
Incolla speciale - Salta spazi vuoti
Ignora spazi vuoti è un'opzione speciale di incolla che non sembra essere utilizzata tutte le volte che dovrebbe. Ti consente di copiare solo celle non vuote durante le operazioni di copia e incolla. Quindi le celle vuote non vengono copiate.
In questo esempio sotto. Copiamo la colonna A, incolliamo regolarmente la colonna B e saltiamo gli spazi vuoti nella colonna C. Puoi vedere che le celle vuote non sono state incollate nella colonna C nell'immagine qui sotto.
| 123 | Fogli("Foglio1").Intervallo("A1:A5").CopiaFogli("Foglio1").Intervallo("B1").PasteSpecial SkipBlanks:=FalseFogli("Foglio1").Intervallo("C1").PasteSpecial SkipBlanks:=True |
Altre opzioni speciali di incolla
Incolla speciale - Commenti
| 1 | Fogli("Foglio1").Intervallo("A1").Copia Fogli("Foglio1").Intervallo("E1").PasteSpecial xlPasteComments |
Incolla speciale - Convalida
| 12 | Fogli("Foglio1").Intervallo("A1:A4").CopiaFogli("Foglio1").Range("B1:B4").PasteSpecial xlPasteValidation |
Incolla speciale - Tutto utilizzando il tema sorgente
| 123 | Cartelle di lavoro(1).Fogli("Foglio1").Intervallo("A1:A2").CopiaCartelle di lavoro(2).Fogli("Foglio1").Intervallo("A1").PasteSpecialCartelle di lavoro(2).Fogli("Foglio1").Intervallo("B1").PasteSpecial xlPasteAllUsingSourceTheme |
Incolla speciale - Tutti tranne i bordi
| 123 | Intervallo ("B2: C3"). CopiaIntervallo ("E2"). Incolla specialeIntervallo ("H2"). PasteSpecial xlPasteAllExceptBorders |
PasteSpecial - Larghezze delle colonne
Uno dei miei preferiti personali. PasteSpecial Column Widths copierà e incollerà la larghezza delle colonne.
| 123 | Intervallo ("A1: A2"). CopiaIntervallo ("C1"). Incolla specialeRange("E1").PasteSpecial xlPasteColumnWidths |
PasteSpecial - All MergingConditionalFormats
| 123 | Intervallo ("A1: A4"). CopiaIntervallo ("C1"). Incolla specialeIntervallo ("E1"). PasteSpecial xlPasteAllMergingConditionalFormats |
