Portfolio - automatisch invullen tabbladen

[wout83]

Hi Allen

Heb weer een vraagje over Excel

Bijgevoegd bestand zal eea verduidelijken.

Vraag 1:
Als de waarde in kolom A (Portfolio) niet gelijk is aan NO (dus alle andere waarden) dan moeten de volgende kolommen gekopieerd (als slave) worden naar:

tabblad SALES:
Portfolio / Type / Brand / Model / Color / Data / Reference / EAN-code
Alsook de hoofding in het zwart uiteraard. In SALES komen er nog kolommen bij (die ik manueel toevoeg) waarvan ik de gegevens haal uit een draaitabel.

tabblad STOCK:
Portfolio / Type / Brand / Model / Color / Data / Reference / EAN-code
Alsook de hoofding in het zwart uiteraard. In STOCK komen er nog kolommen bij (die ik manueel toevoeg) waarvan ik de gegevens haal uit een draaitabel.

Als ik dus een veld aanpas in het tabblad PORTFOLIO dan moeten deze ook wijzigen over de andere tabbladen. (master)
In de andere tabbladen mogen geen wijzigingen gebeuren in deze specifieke kolommen (slave)

Vraag 2:
Zou in PORTFOLIO tabblad ook nog een aantal knoppen willen die automatisch bepaalde kolommen verstuurd per mail.
De kolommen wil ik later zelf kunnen bepalen + ook definiëren naar welke distributielijst ik deze wens te versturen.

Andere vragen ga ik stellen in nieuwe threads om het overzicht te kunnen bewaren.

Thanks
Wout

Bekijk bijlage test_portfolio.xlsx

 

[wieter]

Mischien voor je 1ste vraag een oplossing.
Bij een wijziging in blad "PORTFOLIO" worden de andere bladen aangepast.
De bladen SALES en STOCK zijn beveiligd tegen wijzigingen, je moet wel vooraf bepalen welke kolommen je wil vrijgeven.

Bekijk bijlage 183078

 

[wout83]

Hi Wieter

Dit ziet er heel goed uit.
Code gevonden en ga deze eens inspecteren
Dikke merci!

Hoe kan ik deze nu uitvoeren in mijn origineel bestand?
Kan de vba code wel kopieren maar niet uitvoeren? (zal wel iets simpel zijn maar ik zie t niet)

 

[wieter]

Zet de code in je origineel bestand achter blad 1(PORTFOLIO)
De eerste regel zorgt er dan voor, dat bij elke wijziging op blad PORTFOLIO de code start.
Zorg ervoor dat de namen van je tabbladen (in je originele bestand) exact overeenkomen met de namen in de code.

Private Sub Worksheet_Change(ByVal Target As Range)

Je wil dat de bladen SALES en STOCK gedeeltelijk beveiligd zijn tegen wijzigingen.
Haal dan in blad PORTFOLIO via celeigenschappen de beveiliging weg, bij de cellen die je wil vrij geven.

Je tweede vraag, een selectie van kolommen mailen en dan ook nog een keuze in de mailadressen maken, is een ander paar mauwen!!!!!
Zelf ga ik altijd te rade op de site van Ron De Bruin.

 

[wout83]

Hi Wieter

Bedankt voor je antwoord.
Je bedoelt dus dat ik de code die je hier schrijft moet toevoegen in de VBA editor?

De namen van de tabbladen zijn exact hetzelfde.
Stel dat ik nu kolom EAN niet wens weer te geven in Sales tab, welke aanpassing moet ik dan doorvoeren in je code?

-edit-
ik had die code al in vba editor staan.
Maar als ik de code save en het bestand bewaar als excel met macro dan zie ik de andere tabbladen nog niet automatisch ingevuld worden.

-edit2-
Ik heb nu een nieuwe rij toegevoegd en door deze actie zijn de andere velden gekopieerd.
Dat leek de trigger te zijn maar toch stel ik me de vraag of hier ook geen knop voor bestaat?

 

[wieter]

Met een knop de code starten (zie bijlage)
De code in mijn vorige posting was alleen maar de eerste regel(de event) van de hele code.

Bekijk bijlage 183136

 

[wout83]

Thanks Wieter.
Waar kan ik deze knop configureren qua plaats en tekst?
gevonden

--edit--
Trouwens, nieuwe rijen toevoegen gaat best langzaam, kan dit op een of andere manier versneld worden? (OPGELOST - via knop gaat alles veel sneller )

Deze vraag blijft open:
Hoe moet ik nu bv EAN kolom verbergen op de andere tabbladen?

 

[wout83]

Tevens merk ik ook dat ik mijn origineel bestand in de andere tabbladen geen data kan ingeven aangezien het hele blad is beveiligd.
In het voorbeeld bestand werkt het perfect maar niet als ik de code kopieer naar origineel.
Ligt dat aan specifieke instellingen in Excel voor mijn document of zie ik iets over het hoofd?

 

[wieter]

Het verbergen van de kolom van de EAN-code ben ik nog aan het uitzoeken om dit via de code te doen.
Hoe de beveiliging van sommige kolommen in de andere bladen opheffen?
- Vanuit het blad PORTFOLIO worden de gewenste regels gecopieërd naar de andere tabbladen die beveiligd zijn.
- In het blad Portfolio moet je dus vooraf instellen welke kolommen er moeten vrijgegeven worden.
- Dit doe je door (via de celeigenschappen) het vinkje van de beveiliging weg te halen bij de kolommen die je vrij wil hebben.
Selecteer dus alle kolommen vanaf kolom M tot zover als je wil, open celeigenschappen, en haal bij beveiliging het vinkje weg.
- De bewuste regels worden dan gecopieërd met cellen die niet beveiligd zijn.

Dat de code met een Change-event, trager zou werken dan met een knop betwijfel ik.

 

[wout83]

Thx Wieter, dat met die celeigenschappen wist ik dus niet.
Change-event in het originele bestand met meer regels werkt dus trager maar dat is opgelost door de knop en werkt prettig.

 

[wieter]

De kolom met de EAN-code wordt nu in de andere bladen verborgen.
Bijlage met change-event en bijlage met een knop.
Volgens mij werkt het change-event onmiddelijk(sneller) dan met een knop die telkens moet ingedrukt worden(Het is trouwens dezelfde code).

Bekijk bijlage 183158

Bekijk bijlage 183156

 

[wout83]

OK thanks
Maar nu is wel het volgende een probleem...
Als ik nieuwe kolommen aanmaak in bv tabblad sales dan gaat excel deze wissen na change event of knop.

Dit mag natuurlijk niet gebeuren.

 

[wieter]

Je opmerking is terecht.
Ik had er geen rekening mee gehouden dat de bladen "sales" en "stock" telkens gewist werden.
Je bezwaar dat bij veel regels de code zeer traag werd, was ook terecht, daar alle regels moesten doorlopen worden.

Daarom een geheel nieuwe code.
Wanneer je nu in "portfolio" een [No] verandert in iets anders, wordt die regel alleen gecopieërd.
De cellen zonder beveiliging bijven ook nu beschikbaar, en worden niet meer gewist.

Hopelijk kunnen de professionals die deze draad volgen, mijn code optimaliseren????

Bekijk bijlage 183190

 

[wout83]

OK bedankt!

De opmaak zou wel over de hele rij moeten worden gekopieerd aangezien het anders niet duidelijk is qua selectie.
Is dit iets wat moet worden opgenomen in vba?

 

[wieter]

Dit zou ik doen met voorw.opmaak, zoals je in blad1 gedaan hebt.

 

[wout83]

OK.
Maar als ik nu een veld in een bestaande rij aanpas dan zie ik deze niet meteen updaten naar de andere tabs.
Hoe kan dit?

Tevens als ik een rij verwijder dan krijg ik een foutmelding over foutopsporing dus er lijkt nog iets fout te gaan.

 

[wout83]

Ik probeer het nu via manuele knop te doen maar dat werkt niet echt...

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
    Sheets("SALES").Unprotect
    Sheets("STOCK").Unprotect
    If Target.Column = 1 And Target.Value <> "NO" Then
        Target.Offset(, 0).Resize(, 12).Copy Sheets("SALES").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        Target.Offset(, 0).Resize(, 12).Copy Sheets("STOCK").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    End If
    Sheets("SALES").Columns("A:L").EntireColumn.AutoFit
    Sheets("SALES").Columns("H:H").EntireColumn.Hidden = True
    Sheets("STOCK").Columns("A:L").EntireColumn.AutoFit
    Sheets("STOCK").Columns("H:H").EntireColumn.Hidden = True
    Sheets("SALES").Protect
    Sheets("STOCK").Protect
Application.ScreenUpdating = True
    
End Sub

Dan krijg ik foutmelding.

 

[wieter]

Wout,
Mijn VBA-kennis blijkt onvoldoende te zijn om je wensen perfect opgelost te krijgen.
Toch nog een poging.
Ik heb toch terug de eerste code gebruikt.
Elke wijziging in blad "PORTFOLIO" zal zorgen voor een update van de andere bladen, alleen de kolommen A t.e.m. L worden nu overgezet, dus de waarden die je aanbrengt in de andere kolommen worden niet meer gewist.
Je moet alleen nog met voorwaardelijke opmaak de volledige rijen kleuren (zoals in blad PORTFOLIO).
Het nadeel van de code is dat, wanneer het over honderden rijen gaat, de code langzaam wordt.
Indien deze manier je geen voldoening geeft, zal ik de handoek in de ring moeten gooien.
Hopelijk schieten de echte kenners je alsnog ter hulp.

Twee bijlagen: 1 met knop en 1 zonder knop

Bekijk bijlage 183298
Bekijk bijlage 183300

 

[wout83]

Wieter, dikke merci, echt waar.
Uw kennis blijkt tot nu toe voldoende te zijn en dat apprecieer ik enorm.

Net nog wel een leuke, als ge een rij op hidden zet in bv sales dan stopt deze code met kopieren vanwege de selectie.
Dus met tel selectie zou dat weg zijn...

Anyway, heb niets te verbergen dus deze code werkt.
Als ik meerdere kolommen wens dan maak ik mijn initieel bereik groter en dan verberg ik meer kolommen bij het overzetten

Nogmaals bedankt - op naar het weekend en de volgende uitdaging.

 

[wout83]

Kan er iemand mij nog verder helpen?
Ik zou graag de filter die ik in het eerste tabblad heb geplaatst automatisch ook laat toepassen in de volgende tabbladen.
Geen idee hoe ik dit moet toepassen.

Ik heb de code van Wieter ook een beetje aangepast:

Private Sub CommandButton1_Click()
Dim i As Integer
i = 3
With Sheets("SALES")
Application.ScreenUpdating = False
.Unprotect
.Range("A1:L" & .Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents
For Each cl In Sheets("PORTFOLIO").Range("a:a").SpecialCells(2)
If cl.Value <> "NO" Then
i = i + 1
'cl.Offset(, 0).Resize(, 12).Copy .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
cl.Offset(, 0).Resize(, 12).Copy .Cells(.Cells(i, 1).End(xlUp).Row + 1, 1)
.Columns("A:L").EntireColumn.AutoFit
End If
Next
.Columns("H:H").EntireColumn.Hidden = True
'.Protect
End With
With Sheets("STOCK")
Application.ScreenUpdating = False
.Unprotect
.Range("A1:L" & .Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents
For Each cl In Sheets("PORTFOLIO").Range("a:a").SpecialCells(2)
If cl.Value <> "NO" Then
cl.Offset(, 0).Resize(, 12).Copy .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
.Columns("A:L").EntireColumn.AutoFit
End If
Next
.Columns("H:H").EntireColumn.Hidden = True
'.Protect
End With
End Sub