Excel: formules meenemen, maar losse data niet

[pwlekkerkerk]

Mijn vraag
In Excel 2010 probeer ik collega's een rij te laten invoegen als ze extra data hebben in een plannings-file. Het probleem was dat er dan geen formules meegekopieerd worden van de rijen daarboven. Dat heb ik opgelost via deze macro:

https://www.pc-helpforum....bij-invoegen-van-een-rij/
Alle formules in de file zijn wel 'geblokkeerd' tegen foutief bewerken.

Het punt is dan alleen dat niet alleen de formules, maar ook de data die handmatig was ingevoerd wordt meegekopieerd. Wie kan me hierbij helpen om dat te voorkomen? Dus dat alleen de formules worden meegekopieerd?

Groet,

Pieter

[Bastiaan]

Kun je de data niet converteren naar een tabel? Dan wordt er automatisch een rij toegevoegd zodra je data begint in te voeren, wordt opmaak mee doorgevoerd én worden de formules meegenomen zonder de inhoudelijke data. Hoef je ook niet te rotzooien met macro's, omdat tabellen standaard feature in Excel zijn.

[ Voor 17% gewijzigd door Bastiaan op 24-02-2020 09:05 ]

[dix-neuf]

Van een ingevoegde rij zul je altijd maar een deel gebruiken.
Hieronder gebruik ik de kolommen A t/m G.

code:

Sub Rij_Invoegen()
Dim ar As Integer, y As Integer
ar = ActiveCell.Row
With Sheets("Blad1")
.Rows(ar + 1).EntireRow.Insert
Set myrange = .Range("a" & ar & ": g" & ar)
For y = 1 To 7
If .Cells(ar, y).HasFormula Then
.Cells(ar, y).Copy Cells(ar + 1, y)
End If
Next y
End With
End Sub

Let op met de formules. Staat in een cel in de rij die je kopieert bv. =E1, dan wordt dat in de ingevoegde rij eronder = E2; staat in de cel in de te kopiëren rij =E$1, dan wordt het ook in de rij eronder ook =E$1.

[pwlekkerkerk]

dix-neuf schreef op dinsdag 25 februari 2020 @ 04:10:
Van een ingevoegde rij zul je altijd maar een deel gebruiken.
Hieronder gebruik ik de kolommen A t/m G.

code:

Sub Rij_Invoegen()
Dim ar As Integer, y As Integer
ar = ActiveCell.Row
With Sheets("Blad1")
.Rows(ar + 1).EntireRow.Insert
Set myrange = .Range("a" & ar & ": g" & ar)
For y = 1 To 7
If .Cells(ar, y).HasFormula Then
.Cells(ar, y).Copy Cells(ar + 1, y)
End If
Next y
End With
End Sub

Let op met de formules. Staat in een cel in de rij die je kopieert bv. =E1, dan wordt dat in de ingevoegde rij eronder = E2; staat in de cel in de te kopiëren rij =E$1, dan wordt het ook in de rij eronder ook =E$1.

Bedankt! Dit is wat ik zocht. Het enige 'probleem' is nu nog wel dat als ik dan kolom G (waar de formule in staat) blokkeer tegen bewerken, dat hij bij het uitvoeren van de Macro dit ook blokkeerd , en dus de formule niet mee kopieerd. Iemand daar nog een tip voor?

[pwlekkerkerk]

Bastiaan schreef op maandag 24 februari 2020 @ 09:04:
Kun je de data niet converteren naar een tabel? Dan wordt er automatisch een rij toegevoegd zodra je data begint in te voeren, wordt opmaak mee doorgevoerd én worden de formules meegenomen zonder de inhoudelijke data. Hoef je ook niet te rotzooien met macro's, omdat tabellen standaard feature in Excel zijn.

Hoe bedoel je dit? Ik ben op zoek naar de meest makeklijke optie, waarbij collega's de geblokkeerde cellen (alle cellen waar een formule in staat worden geblokkeerd tegen onjuist aanpassen of in plaats van een formule met de hand een getal invullen) niet kunnen aanpassen, maar wel een rij kunnen toevoegen waarbij de formule dus mee gaat.

[dix-neuf]

Bedankt! Dit is wat ik zocht. Het enige 'probleem' is nu nog wel dat als ik dan kolom G (waar de formule in staat) blokkeer tegen bewerken, dat hij bij het uitvoeren van de Macro dit ook blokkeerd , en dus de formule niet mee kopieerd. Iemand daar nog een tip voor?

Dat kun je oplossen door aan het begin van de macro de beveiliging met vba-code weg te halen en die aan het eind van de macro met vba-code weer in te schakelen.

[ Voor 5% gewijzigd door dix-neuf op 25-02-2020 23:12 ]

[Bastiaan]

pwlekkerkerk schreef op dinsdag 25 februari 2020 @ 10:47:
[...]


Hoe bedoel je dit? Ik ben op zoek naar de meest makeklijke optie, waarbij collega's de geblokkeerde cellen (alle cellen waar een formule in staat worden geblokkeerd tegen onjuist aanpassen of in plaats van een formule met de hand een getal invullen) niet kunnen aanpassen, maar wel een rij kunnen toevoegen waarbij de formule dus mee gaat.

Wanneer je de alle cellen ontgrendeld (in celeigenschappen > bescherming), vervolgens de cellen/kolommen met formules selecteert, alleen die cellen via dezelfde weg vergrendeld en de tabel maakt (start > 'opmaken als tabel') zou dat geen probleem moeten zijn. In een nieuwe rij kun je vervolgens beginnen met tikken waarna die opmaak en vergrendeling wordt meegenomen.

Edit: als laatste stap ook nog even het blad beveiligen. Dat kan gewoon met de standaard instellingen die je dan krijgt, maar anders heeft het vergrendelen nog geen nut

[ Voor 9% gewijzigd door Bastiaan op 26-02-2020 09:51 ]