macro voor aantal functies

[arjoderoon]

ik heb een macro nodig die een aantal dingen doet.
ik heb al een standaard koppeling staan naar een ander blad voor een hoop cellen maar eigenlijk wil ik iets van een importfunctie maken in excel die alle data van een blad overhevelt naar dezelfde kolommen en cellen in het bestand, vervolgens overal de overbodige spaties van een bepaald bereik eruit haalt en dan al deze data ook nog eens middels de tekst naar kolommen functie, omzet naar een getal, en als het even kan: tot slot ook nog saved.

de vraag is: wie kan mij hiermee helpen?

het bestand is opgebouwd als volgt:
in kolom A,B en C staan een aantal artikelen met karakteristieken. Vervolgens komten er in de kolommen D tot en met AC een hele lijst met afnemers.
in de cellen D2 tot en met AC218 staat vervolgens de afname per artikel van de betreffende afnemer.
dit moet geimporteerd worden op het werkblad vanuit het bestand SAP.xls
(het bestand sap.xls is een standaard uitdraai en wordt op het moment dat het nodig is, opnieuw opgeslagen).

Als de data geimporteerd is, moet voor de cellen d2 tot en met ac218 alle overbodige spaties verwijderd worden en vervolgens moeten de cellen in deze waarden omgezet worden zodat het data (getallen) is waarmee gerekend kan worden.

Wie kan een dergelijk script voor mij maken? Ik kan hetzelf opzich middels een macro opname functie in excel zelf, maar dat zal dan verre van efficient zijn...

Eigenlijk wil ik het hele riedeltje onder 1 functie en daarmee onder 1 button in excel hangen.

 

[sylvester-ponte]

laat eens zien wat je hebt tot nu toe

dat is makkelijker voor de helpers.

groet sylvester

 

[arjoderoon]

ik heb even 2 voorbeeld bestandje gemaakt.
voorbeeld 1 is de uitdraai zoals ik deze uit SAP krijg.
voorbeeld 2 is het bestand waarop de data geimporteerd moet worden

in voorbeeld 2 staan 2 tabbladen: 1 heet DATA SAP
het tweede heet Data %. Op dit tabblad heb ik nu een button met de naam 'Import DATA'

door op deze knop te drukken moet het script gaan lopen.
Op het moment dat het script gaat lopen wordt de data in cel A1 op het blad voorbeeld 1 geimporteerd in het blad voorbeeld 2 op het tabblad Data 2 cel A1.
enzovoorts voor de data in cel A2, A3 etc en B1, B2, B3 etc etc etc

vervolgens moet de data op het blad voorbeeld helpmij 2.xls; tabblad Data SAP, in het bereik D1:AC218, ontdaan worden van alle overbodige spaties en vervolgens per kolom de data omgezet worden middels tekst naar kolommen zodat de waarden getallen zijn.

onderstaande macro code is de code die macro-opname functie van excel gemaakt heeft (maar dan in het kort, het aantal rijen en kolommen is in het echt een stuk groter).
deze code werkt vanaf het 2e tabblad maar moet de data naar het 1e tabblad overzetten. dat werkt echter nog niet.

Sub import()
'
' import Macro
' De macro is opgenomen op 27-12-2010 door Arjo de Roon.
'
' Sneltoets: CTRL+q
'
    Windows("voorbeeld helpmij 1.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("voorbeeld helpmij 2.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("D2:F3").Select
    Application.CutCopyMode = False
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("D2:D3").Select
    Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    Range("E2:E3").Select
    Selection.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    Range("F2:F3").Select
    Selection.TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    ActiveWorkbook.Save
End Sub

Bekijk bijlage voorbeeld helpmij 1.xls Bekijk bijlage voorbeeld helpmij 2.xls

 

[arjoderoon]

aanvullend op de macro heb ik nog een (aantal) andere macro('s) nodig.
omdat de sortering top down moet zijn en deze per afnemer kan verschillen heb ik eigenlijk een 2e macro nodig, die in het bestand voorbeeld helpmij 2.xls, op het tabblad Data %, per afnemer de data top down sorteert, en vervolgens de data wegschrijft als waarde, hiermee uitrekent wat het percentage is van de totale afzet, waarna hetzelfde gedaan wordt voor de volgende afnemer etc. om ook hier een beter beeld bij te geven, heb ik het voorbeeld 2 nog aangevuld en opnieuw geupload (zie bijlage bij dit bericht).

 

[sylvester-ponte]

hoi arjo

even bij het begin beginnen:
wat bedoel je met:

en dan al deze data ook nog eens middels de tekst naar kolommen functie, omzet naar een getal,

de functie tekst naar kolommen maakt geen getallen van tekst.
het splitst je tekst op in deelteksten en zet deze deelteksten in de aangrenzende kolommen.

volgende keer meer maar eerst dit oplossen.
groet sylvester

 

[arjoderoon]

ik gebruik (wellicht ten onrechte) altijd tekst naar kolommen om ervoor te zorgen dat de waarden in een kolom altijd dezelfde opmaak indeling hebben als in een ander blad, zodat vert.zoeken geen problemen heeft met vinden van waarden. maar goed, dat is iets anders. ik heb een macro die de data kopieert en plakt.

dit gaat redelijk rap.

Sub import()
'
' import Macro
' De macro is opgenomen op 27-12-2010 door Arjo de Roon.
'
' Sneltoets: CTRL+q
'
    Windows("voorbeeld helpmij 1.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("voorbeeld helpmij 2.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("D2:F3").Select
    Application.CutCopyMode = False
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("D2:D3").Select
    Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    Range("E2:E3").Select
    Selection.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    Range("F2:F3").Select
    Selection.TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    ActiveWorkbook.Save
End Sub

bovenstaande code moet echter voor een hele hoop kolommen en rijen gedaan worden en daarom kan het volgens mij veel efficienter, maar ik weet niet hoe ik dat moet maken/schrijven.

ik zit nu tevens met een ander probleem maar dat gaat denk ik te ver voor het excell forum en is meer een vba item.

 

[cow18]

eigenlijk moet je die SAP-file niet als een gewone kopie naar je 2e blad kopieren, maar gewoon onder elkaar ieder item uitschrijven en daarna daar wat draaitabellen op los laten. Wat ik tot hier gezien heb, wil je straks daar nog meer info uit destilleren, dan, ben je alleszins al op de goede weg.
Hoe veel rijen bevat je SAP-file en met welke versie van excel werk je ?

zie voorbeeld
Sap wordt omgezet naar database en daar krijg je zo 2 draaitabellen uit

 

[arjoderoon]

Mijn SAP file bevat (nu) 209 rijen. Ik werk met excel 2000 (maar vanaf jan 2011 hopelijke een nieuwere versie).

helaas gaat vba me (nog) boven de pet en heb ik daardoor niet het inzicht hoe ik dingen het beste kan organiseren.

als ik de vraag die ik in het vba forum gesteld heb ook meeneem, dan is de moraal van het verhaal eigenlijk dat ik volgens mij, om een goede sortering per klant te kunnen maken, alle data per klant ook op een apart tabblad laat zetten en vervolgens daar vandaan ga sorteren en rekenen.

Maar ik weet dat de macro recorder vaak heel inefficiente code produceert waardoor bestanden onnodig traag worden.

wat zou de suggestie zijn om het beter te doen?

ik snap niet helemaal wat je bedoelt namelijk met :

eigenlijk moet je die SAP-file niet als een gewone kopie naar je 2e blad kopieren, maar gewoon onder elkaar ieder item uitschrijven en daarna daar wat draaitabellen op los laten.

want als ik per klant de info wil kopieren plakken, moet ik dan van bijv. 20 verschillende klanten een aparte macro maken waarmee de data van de sap file naar een nieuwe worksheet gekopieerd wordt, de nieuwe worksheet wordt gerenamed en de data gesorteerd?

 

[arjoderoon]

ik ben bang dat de suggestie voor mij niet werkt. ik moet na het importeren van de data nog per klant een sortering maken. Want deze data wordt nog in een ander hoofddocument opgehaald.

daarbij is de structuur van het blad DATA SAP behoorlijk vast. ik heb niet de mogelijk om dat soort dingen te wijzigen.

dus voor mij wordt het misschien wel het vaak kopieren uitvoeren van een formule naar veel werkbladen: de kolommen a t/m c moeten altijd gekopieerd worden en verder vanaf kolom D moet deze kolom telkens 1 opschuiven naar rechts. (en telkens op een nieuw werkblad).

hiermee kan ik dan vervolgens aan de slag om aflopend te sorteren, percentages uit te rekenen en ook cumulatieve percentages te berekenen. Deze heb ik namelijk nodig (per artikel, per klant).

in het voorbeeld staat wat ik heb en wat ik zou moeten hebben. ik ben alleen op zoek naar het proces ertussen om op het eindresultaat uit te komen.

(data cum)

 

[cow18]

ik ben bang dat ... je niet begrijpt hoe krachtig draaitabellen zijn om jou die gegevens in een handomdraai te verschaffen die je anders met oneindig kopieren en plakken zou moeten verzamelen.
In bijlage 3 draaitabellen, ik kan er zo nog een paar verzinnen, die de gegevens nog anders voorstellen, het is alleen maar de vraag wat je wil, maar ga er van uit het gezegde "the sky is the limit" van toepassing voor draaitabellen ivm. jouw probleem.

 

[arjoderoon]

als draaitabellen mij de info kunnen leveren in de vorm die ik kan gebruiken dan vind ik het prima. het gaat mij er niet om dat het perse een vba script is die de bewerkingen doen.

alleen ben ik nog niet zover dat ik voor elkaar krijg dat ik in de draaitabellen een aflopende sorteren op de verkopen krijg en dat vervolgens ook de cumulatieve percentages erbij gezet worden.

stap 2 is vervolgens dat ik op een ander blad op basis van de resultaten per klant, in de assortimentslijst aan kan geven wat het cumulatieve percentage is (en op basis daarvan krijg een artikel een bepaalde waarde, bijvoorbeeld als deze kleiner is dan 80% dan krijgt het artikel voor die klant een 1, anders een 2).

 

[cow18]

Ga er eventjes van uit dat je een draaitabel krijgt die je ergens in een uithoek van de map zet en dat die in een lus alle gegevens per afnemer naar aparte werkbladen schrijft en daar alle nodige toeters en bellen aan toevoegt. Met die gegevens kan je dan nog verder een aantal bewerkingen doen tot het resultaat voldoet.
Het probleem van cumulatief dat ik tot hiertoe nog niet goed zie hoe je cumuleert, daarvoor zijn de gegevens wat summier.
Verder wil ik met totaal niet opdringen, als je een plan B hebt, mag je die optie zeker uitproberen.

 

[arjoderoon]

cumuleren doe ik door per klant de artikelen aflopend te sorteren op de verkopen. daarna reken ik uit hoeveel % ieder artikel van het totaal voor zijn rekening neemt.
omdat er een grens getrokken wordt bij bijvoorbeeld 80% van de totale afzet, moet ik weten wanneer ik op 80% zit. Als artikel 6 bijvoorbeeld cumulutief op 85% zit, wordt deze niet meegenomen. Alle artikelen die samen voor maximaal 80% van de afzet zorgen, worden wel meegenomen en krijgen een bepaalde waarde, bijvoorbeeld 1.

Maar per klant, kan de sortering obv afzet verschillen. dus dat probleem moet ook nog meegenomen worden.

daarbij moet het zo zijn, dat het zo gebruiksvriendelijk moet zijn dat iemand die totaal blanco is qua opbouw, het bestand ook gebruiken kan. die hoeft alleen maar op een knop te drukken en de data wordt opnieuw opgehaald, en vervolgens gesorteerd etc.

 

[arjoderoon]

door een draaitabel kan ik zelf al aflopend laten sorteren op de verkopen
ik moet nu de volgende dingen maken:
-een aparte worksheet per klant (alleen voor die klanten die relevant zijn)
-op die worksheets moeten automatische draaitabellen komen waarbij de draaitabel op een bepaalde manier opgemaakt is (waarden weergeven als % van totaal)
deze draaitabellen zijn gebaseerd op de data op het tabblad data sap


-cumulatief percentage erbij zetten.

-vervolgens in het hoofdbestand per klant laten zoeken op het juiste tabblad

kan ik bovenstaande middels een VBA script laten doen/ dat hij ook checked voor welke klanten er een tabblad gemaakt moet worden en op data blad vervolgens voor de betreffende klanten een draaitabel maakt (het bereik verschil dan per klant).

i need some help hiervoor.

 

[cow18]

zie bijlage
2 knoppen :
- de 1e om van je SAP-gegevens de database aan te maken en van daaruit de draaitabel
- de 2e om vanuit die draaitabel alles naar de klantenbladen te zenden.
De truuk zit erin om de klant in het paginagedeelte te zetten en dan te filteren per klant en die gefilterde gegevens dan door te sturen naar het klantenblad.

 

[arjoderoon]

je bent een held.


dit is wat ik nodig heb. als de knoppen op een ander blad geplaatst worden, is het blad draaitabel voor mij verder niet nodig. of wordt deze nog gebruikt binnen het script?

ik kan dan in het hoofdbestand per tabblad laten zoeken op klantnaam en de juiste waarde op laten halen (doel is om nog maar 1 bestand te hebben waar alles bij elkaar zit, middels jouw script gaat dat ook lukken)
(is daar ook een code voor? een soort vert.zoeken maar dan eerst zoekend op tabblad naam en daarna binnen dat blad de juiste waarde ophalend...?)

ik heb nog wel een aanvulling:
-er hoeft niet voor iedere klant in het bronbestand een draaitabel gemaakt te worden, maar alleen voor de klanten die in een bepaalde lijst staan.
Hoe krijg ik dit er nog ingefietst?

ik ga je suggestie / voorbeeld overnemen en gebruiken!

nu moet ik het nog leren om zelf zoiets te kunnen maken

 

[cow18]

je praat altijd om de tabellen dan weer samen te voegen voor een ander hoofdbestand. Dat is toch stom, want alle gegevens zijn al verzameld door de draaitabel.
Bon, ik heb de 2 knoppen verhuisd naar een nieuw tabblad "MijnTabel" die alle gegevens verzamelt van bepaalde klanten die vermeld staan in de kolom M van "MijnTabel". Er wordt ook maar voor die klanten een werkblad aangemaakt.
De draaitabel is broodnodig, die doet het vuile werk om de gegevens te verzamelen, maar het werkblad is verborgen, zodat je het niet meer opmerkt.

 

[arjoderoon]

dank je...

ik heb nog 1 kleine aanpassing nodig.
het script gaat er nu vanuit dat alle data al op het blad data sap staat.
in de werksituatie is dit een apart xls bestand dat iedere keer opgeslagen wordt als er een update gedraaid moet worden.

ik had dit zelf uitgevogeld, met

Workbooks.Open Filename:="sap.xls"
    Cells.Select
    Selection.Copy

kan ik dit er zo in plakken nu?

dus in plaats van dat het script de data van het blad data sap haalt, moet de data vanuit het bestand sap.xls gehaald worden.

(zie de bijlage voor hoe het bestand opgemaakt is, het wordt standaard vanuit SAP opgeslagen in wk1 opmaak. en bij negatieve waarden zitten er een hele hoop spaties in, vandaar dat alle spaties er ook uitgehaald moeten worden).

(ms visual basic geeft in het script een error bij: )

          sh.Range("A1").PasteSpecial xlPasteColumnWidths

 

[cow18]

je zou anders ipv in deze cel je draaitabel kunnen laten verwijzen naar een externe bron, dan ben je af van dat kopieren en plakken. Noemt die file altijd hetzelfde ?

op het moment dat je kopieert sta je vermoedelijk nog in SAP en moet je er dus bij vertellen dat hij het naar de map met je draaitabel moet kopieren met ...
Thisworkbook.sh.Range("A1").PasteSpecial xlPasteColumnWidths.

 

[arjoderoon]

de file heeft altijd dezelfde bestandsnaam en wordt altijd opgeslagen in dezelfde map als waarin het bestand met de draaitabellen/script zal staan