Kopier formler til andet regneark uden links

Hvordan undgår man links eller referencer til det regneark, man kopierer fra, når man kopierer celler med formler til et andet ark?

På denne side viser jeg et eksempel på en makro, der kan gøre det, men først en forklaring på, hvorfor det kan være et problem.

Hvis du i regnearket "formler" kopierer en celle med formlen "=jan2016!E4", som henviser til celle E4 på fanebladet "jan2016", vil formlen i det regneark, du kopierer til, se sådan ud:

Altså med et link eller reference til regnearket, du kopierede fra. Og hvad gør man så lige, hvis man kun vil have formlen "=jan2016!E4" uden referencen til "formler.xls"?

Ja, man kan lave en søg og erstat, hvor man erstatter "[formler.xls]" med ingenting, men man kan også bruge en makro til kopieringen, og herunder viser jeg et eksempel på hvordan. Det er smart, hvis det er noget, man gør ofte.

Medmindre man bruger dollartegn i sin formel, vil formlen i øvrigt også ændre sig, hvis man indsætter i en anden celle end den oprindelige.

Altså hvis celle A1 har formlen "=jan2016!E4", og man indsætter i celle B2, når man kopierer, vil den nye formel blive: "=[formler.xls]jan2016!F5"

Det sker ikke med den teknik, jeg demonstrerer nedenfor. Men lad os komme i gang! Hvis du vil prøve makroen, kan du markere teksten med musen, kopier (CTRL+C) og sæt den ind (CTRL+V) i et VBA-modul.

Sub KopierFormler()
Dim bOpen as Boolean
Dim arArray()
Dim rTabel As Range
Dim rTarget As Range
Dim sFileName
Dim Wb As Workbook

On Error Resume Next

'Et range kan defineres på mange
'måder - her beder vi brugeren
'markere området, som skal kopieres.
Set rTabel = Application.InputBox _
("Markér området, som skal kopieres.", _
"Marker område", , , , , , 8)

If Not rTabel Is Nothing Then
   On Error GoTo ErrorHandle
   
   If rTabel.Count = 1 Then
      MsgBox "Der skal være mere end 1 celle"
      GoTo BeforeExit
   End If
   
   'Kopierer formlerne til et array, som
   'automatisk får samme dimensioner som
   'tabellen. Fidusen ligger bl.a. i at
   'skrive "rTabel.Formula". Hvis man
   'bare skrev "rTabel", ville det være
   'celleværdierne, man kopierede, da
   '"Value" er default, hvis man ikke angiver
   'andet.
   arArray = rTabel.Formula
   
   'Viser en fil åbn dialog. Vælg det
   'regneark, som tabellen skal kopieres til.
   'Funktionen GetOpenFileName åbner ikke filen -
   'den returnerer bare det valgte filnavn med
   'stien til filen.
   sFileName = Application.GetOpenFilename _
   ("Excel-filer (*.xls*),*.xls*", , _
   "Vælg den fil, der skal kopieres til")
   
   If sFileName = False Then GoTo BeforeExit
   
   'Tjek, om det valgte regneark er åbent.
   For Each Wb In Workbooks
      If Wb.FullName = sFileName Then
         Wb.Activate
         bOpen = True
         Exit For
      End If
   Next

   'Hvis det ikke var åbent, åbnes det.
   If bOpen = False Then
      Workbooks.Open (sFileName)
   End If

   On Error Resume Next
   
   'Beder brugeren om at klikke på indsætningspunktet
   Set rTarget = Application.InputBox _
   ("Vælg celle for tabellens øverste venstre hjørne", _
   "Indsætningspunkt", , , , , , 8)

   'Hvis brugeren valgte en celle
   If Not rTarget Is Nothing Then
      On Error GoTo ErrorHandle      

      'Dimensioner målområdet, der skal have samme
      'antal rækker og kolonner som arrayet.
      Set rTabel = rTarget.Resize(UBound(arArray), UBound(arArray, 2))
      'Sæt formlerne ind
      rTabel.Formula = arArray
   End If
End If

BeforeExit:
On Error Resume Next
Set rTabel = Nothing
Set rTarget = Nothing
Erase arArray

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Det var det, blot skal du være opmærksom på, at hvis formlerne peger på et faneblad, der fx hedder "jan2016", skal regnearket, du kopierer til, have et faneblad med samme navn - ellers peger formlerne på noget, der ikke eksisterer.

Teknikken med at kopiere et områdes formler på denne måde, har jeg brugt til flere formål, og når man kopierer til og fra et array som her, går det lynhurtigt. På siden Arrays og range-formler har jeg tidligere beskrevet teknikken.

Kopiering af formler mellem regneark