Copiați formulele Excel într-o altă foaie cu VBA

Pentru a copia formule dintr-o foaie de calcul Excel în alta cu VBA, atribuiți intervalele sursă FormulaR1C1unui interval de destinație de aceeași dimensiune. Aceasta transferă modelul de formulă fără a copia formatarea celulelor, iar referințele relative se ajustează pentru celulele de destinație. Macrocomanda de mai jos folosește nume de foi de calcul, deci nu depinde de foaia activă.

Înainte de a începe

Utilizați versiunea desktop a aplicației Excel; Excel pentru web poate deschide un registru de lucru care conține macrocomenzi, dar nu poate crea, rula sau edita macrocomenzi VBA, așa cum se explică în instrucțiunile Microsoft Excel pentru web . Confirmați numele exacte ale filelor și adresele sursă și destinație. În acest exemplu, formulele se află în Source!C2:C10și vor fi plasate în Summary, începând de la C2.

Exemplul utilizează ThisWorkbook, ceea ce înseamnă registrul de lucru în care este stocat codul macrocomenzii. Păstrați macrocomanda în acel registru de lucru sau modificați referința registrului de lucru dacă codul este stocat în altă parte. Numele foilor de lucru sunt selectate din Worksheetscolecția registrului de lucru; numele dintre ghilimele trebuie să corespundă exact cu tabulatorul.

Copiați formulele în altă foaie cu VBA

Deschideți Editorul Visual Basic din fila Dezvoltator din Excel , alegeți Visual Basic , apoi selectați Inserare > Modul . Lipiți această procedură în modul:

Sub CopyFormulasToAnotherSheet()
    Dim wb As Workbook
    Dim sourceRange As Range
    Dim targetCell As Range
    Dim targetRange As Range

    Set wb = ThisWorkbook
    Set sourceRange = wb.Worksheets("Source").Range("C2:C10")
    Set targetCell = wb.Worksheets("Summary").Range("C2")
    Set targetRange = targetCell.Resize(sourceRange.Rows.Count, _
                                        sourceRange.Columns.Count)

    targetRange.FormulaR1C1 = sourceRange.FormulaR1C1
End Sub

Modificați Source, Summary, și adresele de interval pentru a se potrivi cu registrul de lucru. targetCelleste prima celulă de destinație; Resizeface ca destinația să aibă aceeași înălțime și lățime ca sursa, ceea ce este necesar pentru o atribuire cu mai multe celule. Pentru o singură formulă, setați sursa la o singură celulă, cum ar fi C2.

Executați și salvați macrocomanda

1. Plasați punctul de inserare oriunde CopyFormulasToAnotherSheetîn interiorul editorului Visual Basic și apăsați F5 . De asemenea, puteți reveni la Excel, selecta Dezvoltator > Macrocomenzi , alege procedura și selecta Executare .
2. Verificați celulele destinație din Summary. Cu o formulă sursă, cum ar fi =A2*B2în Source!C2, modelul de formulă copiat din Summary!C2este, de asemenea =A2*B2, . Dacă formula ar trebui să continue să citească valori în foaia Sursă, calificați acele referințe în formula sursă, de exemplu =Source!A2*Source!B2.
3. Salvați registrul de lucru ca Registru de lucru Excel cu macrocomenzi activate ( .xlsm) pentru a păstra procedura VBA. Dacă Excel întreabă despre securitatea macrocomenzilor, executați doar macrocomenzile în care aveți încredere.

Alegeți metoda potrivită pentru formulele dvs.

FormulaR1C1copiază structura formulei. O referință la două coloane la stânga celulei formulei rămâne la două coloane la stânga în destinație; referințele absolute rămân fixe. Acest lucru este util atunci când doriți același model de calcul într-un interval de destinație corespunzător. Nu copiază formatarea. Dacă intervalul sursă include constante sau spații goale, acestea sunt transferate și ca valori sau celule goale, deci utilizați un interval alcătuit din formulele pe care intenționați să le copiați.

Pentru comportamentul normal de copiere în Excel, inclusiv formule și formatare, utilizați Range.Copycu o destinație:

wb.Worksheets("Source").Range("C2:C10").Copy _
    Destination:=wb.Worksheets("Summary").Range("C2")

Aceasta copiază întregul conținut al celulei și formatarea; Excel ajustează referințele relative așa cum face în timpul unei copii standard. Folosește-o atunci când destinația ar trebui să primească mai mult decât formule. Microsoft documentează atât proprietatea FormulaR1C1 , cât și metoda Range.Copy .

Depanați problemele comune

• „Indice în afara intervalului”: Verificați dacă numele foilor de lucru sunt scrise exact așa cum apar pe file, inclusiv spațiile.
• Formula indică celulele greșite: FormulaR1C1 conține referințe relative la foaia de destinație. Adăugați un nume de foaie la formulă atunci când aceasta trebuie să continue să indice către foaia sursă.
• Codul folosește registrul de lucru greșit: ThisWorkbook se referă la fișierul care conține macrocomanda. Dacă codul se află într-un registru de lucru personal cu macrocomandi, indicați în wbschimb registrul de lucru deschis după nume.
• Macrocomanda nu este listată: Confirmați că fișierul este salvat ca .xlsmși că macrocomanda este activată pentru un fișier de încredere. Instrucțiunile pentru macrocomanda de la Microsoft explică cum se activează fila Dezvoltator și cum se execută o procedură.

Când numele sursei și destinației, intervalele și comportamentul referinței sunt setate în mod deliberat, macrocomanda poate copia o singură formulă sau un bloc întreg fără a selecta foi sau a se baza pe clipboard.