Excel-formules kopiëren naar een ander werkblad met VBA

Om formules van het ene Excel-werkblad naar het andere te kopiëren met VBA, wijst u het bronbereik toe FormulaR1C1aan een doelbereik van dezelfde grootte. Hierdoor wordt het formulepatroon overgedragen zonder de celopmaak te kopiëren, en relatieve verwijzingen worden aangepast aan de doelcellen. De onderstaande macro gebruikt werkbladnamen, waardoor deze niet afhankelijk is van welk werkblad actief is.

Voordat je begint

Gebruik de desktopversie van Excel; Excel voor web kan een werkmap met macro's openen, maar kan geen VBA-macro's maken, uitvoeren of bewerken, zoals uitgelegd in de handleiding van Microsoft voor Excel voor web . Controleer de exacte tabnamen en de bron- en bestemmingsadressen. In dit voorbeeld bevinden de formules zich in Source!C2:C10en worden ze geplaatst in Summary, beginnend bij C2.

In dit voorbeeld wordt ThisWorkbookde werkmap gebruikt waarin de macrocode is opgeslagen. Laat de macro in die werkmap staan, of wijzig de verwijzing naar de werkmap als de code elders is opgeslagen. Werkbladnamen worden geselecteerd uit de Worksheetsverzameling van de werkmap; de naam tussen aanhalingstekens moet exact overeenkomen met het tabblad.

Formules kopiëren naar een ander werkblad met VBA

Open de Visual Basic Editor via het tabblad Ontwikkelaar in Excel , kies Visual Basic en selecteer vervolgens Invoegen > Module . Plak de volgende procedure in de module:

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

Wijzig de bereikadressen Source, , en zodat ze overeenkomen met uw werkblad. is de eerste bestemmingscel; zorgt ervoor dat de bestemming dezelfde hoogte en breedte heeft als de bron, wat vereist is voor een toewijzing aan meerdere cellen. Voor een enkele formule stelt u de bron in op één cel, bijvoorbeeld .SummarytargetCellResizeC2

Voer de macro uit en sla deze op.

1. Plaats de cursor ergens CopyFormulasToAnotherSheetin de Visual Basic Editor en druk op F5 . U kunt ook terugkeren naar Excel, Ontwikkelaar > Macro's selecteren , de procedure kiezen en Uitvoeren selecteren .
2. Controleer de doelcellen op Summary. Bij een bronformule zoals =A2*B2in Source!C2, is het gekopieerde formulepatroon in Summary!C2ook =A2*B2. Als de formule waarden op het bronblad moet blijven lezen, kwalificeer die verwijzingen dan in de bronformule, bijvoorbeeld =Source!A2*Source!B2.
3. Sla de werkmap op als een Excel-werkmap met macro's ( .xlsm) zodat de VBA-procedure behouden blijft. Als Excel om macrobeveiliging vraagt, voer dan alleen macro's uit die u vertrouwt.

Kies de juiste methode voor uw formules.

FormulaR1C1De formulestructuur wordt gekopieerd. Een verwijzing twee kolommen links van de formulecel blijft twee kolommen links in de bestemming; absolute verwijzingen blijven vast staan. Dit is handig als u hetzelfde berekeningspatroon in een overeenkomend bestemmingsbereik wilt hebben. De opmaak wordt niet gekopieerd. Als uw bronbereik constanten of lege cellen bevat, worden deze ook als waarden of lege cellen overgenomen. Gebruik daarom een ​​bereik dat bestaat uit de formules die u wilt kopiëren.

Voor normaal kopiëren in Excel, inclusief formules en opmaak, gebruikt u de Range.Copyvolgende optie met een bestemming:

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

Hiermee worden de volledige celinhoud en -opmaak gekopieerd; Excel past relatieve verwijzingen aan zoals bij een standaardkopie. Gebruik deze functie wanneer de bestemming meer dan alleen formules moet ontvangen. Microsoft documenteert zowel de eigenschap FormulaR1C1 als de methode Range.Copy .

Los veelvoorkomende problemen op.

• “Index buiten bereik”: Controleer of de namen van de werkbladen exact zo gespeld zijn als ze op de tabbladen staan, inclusief spaties.
• De formule verwijst naar de verkeerde cellen: FormulaR1C1 er worden relatieve verwijzingen naar het bestemmingsblad gebruikt. Voeg een bladnaam toe aan de formule als deze naar het bronblad moet blijven verwijzen.
• De code gebruikt de verkeerde werkmap: ThisWorkbook er wordt verwezen naar het bestand dat de macro bevat. Als de code zich in een persoonlijke macro-werkmap bevindt, verwijs dan wbin plaats daarvan naar de geopende werkmap op naam.
• De macro wordt niet weergegeven: controleer of het bestand is opgeslagen als .xlsmeen vertrouwd bestand en of macro's zijn ingeschakeld. De macro-instructies van Microsoft leggen uit hoe u het tabblad Ontwikkelaar inschakelt en een procedure uitvoert.

Wanneer de bron- en bestemmingsnamen, bereiken en het verwijzingsgedrag bewust zijn ingesteld, kan de macro een enkele formule of een heel blok kopiëren zonder werkbladen te selecteren of het klembord te gebruiken.