Meerdere bereiken vergroten met een knop

[Kishapawpad]

Hoi allemaal,

Ik heb een vraag omtrent sorteren van gegevens in Excel, en zal proberen het zo duidelijk mogelijk te formuleren.

In een excel werkmap heb ik meerdere bladen met gegevens. Deze gegevens hebben verschillende bereik-benamingen en zijn denk ik geschikt voor een tabel indien nodig. De bereiken zijn bepaalde periodes en weken, en gaan over de uren dat werknemers gewerkt hebben in die periode.

Nu wil ik graag een voorblad hebben waarop je makkelijk kunt selecteren welke Periode of Week je wilt zien, en dan alle gegevens naar voren krijgt.
Ik ben al relatief ver, het werkt al, via Macro knoppen word in een veld een bereiknaam ingevuld (In het voorbeeld zijn geen macro knoppen maar gewoon een gegevensvalidatie lijst, dit komt op hetzelfde neer.) en de cellen zoeken dan automatisch in het aangegeven bereik de juiste waarden.

Het probleem is als volgt.. Als er nieuwe werknemers bij komen, dan moet in elk bereik een nieuwe rij worden gemaakt, en op het voorblad een extra tabelrij.

Ik heb geprobeerd een macro te maken om deze handeling met een druk op de knop uit voeren. het lukt me om een specifieke rij boven de onderste rij te plakken, maar dat is niet goed. De onderste rij van een bereik moet gekopieerd worden Onder de laatste rij, en toch in het bereik blijven.

Bijvoorbeeld in bijlage, op het blad "Periodes" is bereik "A1:I6" benoemt "Periode_1". Bij een nieuwe medewerker moet rij "A6:I6" gekopieert worden naar "A7:I7". En dan ook in bereik "Periode_1" zitten.

En bij Periode_2 hetzelfde, en op het blad Weken bij "Week_1" en ga zo maar door. Daarna pas ik handmatig de namen aan.
Ter info, de waarden die nu in in de bereiken staan worden in het origineel ook weer van andere werkbladen afgehaald.

Dit allemaal om dan op het voorblad altijd de juiste gegevens in de tabel te krijgen.


Ik hoop dat ik het goed uitgelegd heb en dat jullie me hierbij kunnen helpen. Als jullie andere ideeën of suggesties te hebben hoor ik het graag!

Voorbeeld bestand:
Bekijk bijlage Uren test.xlsx

 

[VenA]

Hoe komen de gegevens in de tabjes 'Jantje', 'Pietje' etc? Je hebt het over een tabel waar is deze te vinden? Verder heb je het over macro knoppen maar de code laat je voor het gemak maar weg?

Nb volgens mij kan je deze

=ALS(B5="Periode_13";"Periode 13";ALS(B5="Periode_12";"Periode 12";ALS(B5="Periode_11";"Periode 11";ALS(B5="Periode_10";"Periode 10";ALS(B5="Periode_9";"Periode 9";ALS(B5="Periode_8";"Periode 8";ALS(B5="Periode_7";"Periode 7"; ALS(B5="Periode_6";"Periode 6";ALS(B5="Periode_5";"Periode 5";ALS(B5="Periode_4";"Periode 4";ALS(B5="Periode_3";"Periode 3";ALS(B5="Periode_2";"Periode 2";ALS(B5="Periode_1";"Periode 1")))))))))))))

vervangen door

=B5

 

[gast0660]

Hoi,
Ik snap uw verhaal (opbouw) niet echt
vbtje voor Pietje, Jantje, enz heb je iedere keer een ander tabblad (met 1000 medewerkers??? 1000 tabbladen???), maak gewoon een invulformuliertje waar alle gegevens van de medewerkers worden opgeslagen op één tabblad en bekijk zo verder de opbouw voor de rest.
Greetz

 

[HSV]

Anders nog.
=substitueren(b5;"_";" ")

 

[Kishapawpad]

Bedankt voor de reacties, ik begrijp dat het een moeilijk verhaal is, dat is mijn fout.

Hoe komen de gegevens in de tabjes 'Jantje', 'Pietje' etc? Je hebt het over een tabel waar is deze te vinden? Verder heb je het over macro knoppen maar de code laat je voor het gemak maar weg?

Wat zijn tabjes? Ik heb in het originele bestand meerdere bladen, één voor elke maand, waar ik de exacte uren overneem en dan worden deze uitgelezen op Maand, wekelijks en 4-wekelijkse basis, per persoon, met verdeling in toeslag percentages. De tabel is op het voorblad, daar moet alles tevoorschijn getoverd worden. De Macro code's die ik heb gemaakt werken niet en zijn vrij simpel opgenomen; Namelijk door een regel in elk bereik te kopiëren en te plakken, maar daardoor wordt de volgorde van de namenlijsten veranderd.

Overigens wist ik ook niet zeker of ik wel excel met macro's moest uploaden.

Nb volgens mij kan je deze

=ALS(B5="Periode_13";"Periode 13";ALS(B5="Periode_12";"Periode 12";ALS(B5="Periode_11";"Periode 11";ALS(B5="Periode_10";"Periode 10";ALS(B5="Periode_9";"Periode 9";ALS(B5="Periode_8";"Periode 8";ALS(B5="Periode_7";"Periode 7"; ALS(B5="Periode_6";"Periode 6";ALS(B5="Periode_5";"Periode 5";ALS(B5="Periode_4";"Periode 4";ALS(B5="Periode_3";"Periode 3";ALS(B5="Periode_2";"Periode 2";ALS(B5="Periode_1";"Periode 1")))))))))))))

vervangen door

=B5

Nee, maar wat HSV hierboven aangeeft wel. (Substitueren)


Om het heel simpel te stellen.. Ik zoek een macro code die elke keer een rij onder aan elk bereik toevoegt.
M.a.w.; Kies laatste rij van bereik "Periode_1", Kopieer deze rij en voeg toe onder aan het bereik.

Ditzelfde kan ik dan kopiëren voor elk gewenst bereik, denk ik.


Ik zal zo dadelijk het originele bestand waar het om gaat online zetten met macro's en al zo als het nu is.

 

[Kishapawpad]

Hier bijgevoegd de originele werkmap (min persoonlijke gegevens) zoals hij nu is.

Op moment heb ik een tabblad voor elke werknemer maar dit ik wil weghalen en één compleet overzicht maken. Het tabblad Weken is nog niet af en alleen maar een concept.

De vraag is.. hoe maak ik een macro die een nieuwe werknemer toevoegt, zodat ik alleen nog de naam juist moet invullen op het blad Werknemers?


Misschien moet ik een compleet andere constructie gebruiken. Elke suggestie die helpt zou ik erg waarderen.

Hierbij een link naar het bestand via dropbox, omdat hij iets te groot is om als bijlage te voegen. Let op, macro's, maar dit zijn kleine onschuldige macro's.

https://www.dropbox.com/s/hugnrfeppw2z1hl/Bedrijf Uren 2015 klad.xlsm?dl=0

 

[Kishapawpad]

Ik begrijp dat ik wat onduidelijk ben geweest met te veel informatie. Ik probeer het nog een keer simpel te verwoorden.
Ik zou een macro willen dat de laatste rij van een benoemd bereik selecteert en deze er onder plakt.
Het beste wat me lukt:

Sub kopierfout()
'
' kopierfout Macro
'
'
    Sheets("Periodes").Select
    ActiveWindow.SmallScroll Down:=6
    Range("K27:S27").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

Maar deze code select een specifieke cel (in plaats van gewoon onderste rij van Bereik_naam) en plakt hem erboven in plaats van eronder. Als ik hem er onder plakt, dan behoort hij niet meer tot het bereik.

 

[HSV]

Bedoel je zoiets?

Bv. het benoemd bereik is genaamd "naam".

Sub hsv()
Dim oudrng As String, tb As Range, rr As Range
oudrng = Range("naam").Address
   Set tb = Range("Naam").Columns(1)
   Set rr = tb.Cells(tb.Rows.Count)
   rr.EntireRow.Insert
 Range(oudrng).Name = "naam"
End Sub

 

[VenA]

Volgens mij is het nagenoeg onmogelijk om in de huidige opzet datgene te doen wat je vraagt.

Je hebt minimaal 14 tabbladen waarin het bereik aangepast moet worden. De hele invoer van de uren vind ik niet erg handig en volgens mij gaat het een traag bestand worden door alle koppelingen die er tussen de verschillende tabjes zijn.

Jouw hele bestand heb ik gereduceerd tot twee essentiële tabjes nl 'Personeel' en 'Data'.Op basis van de tab 'data' kan je elke rapportage maken die je maar wil. Het is een ander denkproces maar werkt veel efficiënter.

 

[HSV]

Nog eens nagelezen te hebben, denk ik dat je dit bedoelt.

Sub hsv()
Dim tb As Range, rr As Range
   Set tb = Range("periode_1").Columns(1)
   Set rr = tb.Cells(tb.Rows.Count)
   rr.EntireRow.Insert
 [COLOR="#FF0000"]  rr.EntireRow.Copy rr.Offset(-1)[/COLOR]
End Sub

 

[Kishapawpad]

Volgens mij is het nagenoeg onmogelijk om in de huidige opzet datgene te doen wat je vraagt.

Je hebt minimaal 14 tabbladen waarin het bereik aangepast moet worden. De hele invoer van de uren vind ik niet erg handig en volgens mij gaat het een traag bestand worden door alle koppelingen die er tussen de verschillende tabjes zijn.

Jouw hele bestand heb ik gereduceerd tot twee essentiële tabjes nl 'Personeel' en 'Data'.Op basis van de tab 'data' kan je elke rapportage maken die je maar wil. Het is een ander denkproces maar werkt veel efficiënter.

Wil jij misschien een dagje langskomen bij ons op kantoor?! Uurtje factuurtje! :d

Dat is inderdaad een hele interessante opzet!

Hij klopt niet helemaal maar dat kan ik natuurlijk aanpassen. Wat ik er dan nog moet hebben is een makkelijk lijstje waar ik per dag (of week) de uren kan invullen en dat deze dan verwerkt worden in de grote tabel.

Ik moet dit eens goed bestuderen en erover nadenken, als je nog meer suggesties heb hoor ik het graag! Is compensatie aanbieden tegen de forum regels..?!


Ps.: Ik heb wel even de Dropbox link aangepast omdat deze linkt naar een versie waar niet alle persoonlijke gegevens uit zijn. (Oeps)

 

[Kishapawpad]

Nog eens nagelezen te hebben, denk ik dat je dit bedoelt.

Sub hsv()
Dim tb As Range, rr As Range
   Set tb = Range("periode_1").Columns(1)
   Set rr = tb.Cells(tb.Rows.Count)
   rr.EntireRow.Insert
 [COLOR="#FF0000"]  rr.EntireRow.Copy rr.Offset(-1)[/COLOR]
End Sub

Dit klinkt wel als precies dat waar ik naar zoek, maar ik krijg alleen maar Error "400" als ik deze uitvoer?

 

[HSV]

Goedendag moeilijke voornaam.

In de bijlage geen error.
Wil je wel een gehele rij invoegen, of moet dit per periode?
Als dit per periode moet, moet je de code aanpassen naar.....

Sub hsv()
Dim tb As Range, rr As Range
   Set tb = Range("periode_2").Columns(1)
   Set rr = tb.Cells(tb.CurrentRegion.Rows.Count)
   rr.Resize(, 9).Insert
   rr.Resize(, 9).Copy rr.Offset(-1)
End Sub

Als je een tabel had gemaakt, werden de formules direct meegenomen bij het toevoegen van een nieuwe rij.

 

[Kishapawpad]

Goedendag moeilijke voornaam.

In de bijlage geen error.
Wil je wel een gehele rij invoegen, of moet dit per periode?
Als dit per periode moet, moet je de code aanpassen naar.....

Sub hsv()
Dim tb As Range, rr As Range
   Set tb = Range("periode_2").Columns(1)
   Set rr = tb.Cells(tb.CurrentRegion.Rows.Count)
   rr.Resize(, 9).Insert
   rr.Resize(, 9).Copy rr.Offset(-1)
End Sub

Als je een tabel had gemaakt, werden de formules direct meegenomen bij het toevoegen van een nieuwe rij.

Super, bedankt! Deze code werkt nu helemaal. Ik denk dat ik de code voorheen op de verkeerde plek heb ingevoerd, als ik gewoon bij het toewijzen van de macro aan een object klik op "Nieuw" en jouw code plak, werkt hij wel zoals het hoort. Het lukte me ook de code te verdubbelen om gelijk meerdere bereiken te vergroten.

Overigens, met testen heb ik gemerkt dat de formules meegenomen werden in het bereik ook als dit bereik geen tabel is! Of bedoelde je dat niet?

In ieder geval, mijn probleem is hiermee opgelost en ik ben je erg dankbaar!