Gegevens exporteren van Excel naar Excel (Copy/Paste)

[MMellink]

Hallo,

Ik ben al even op zoek naar een betere methode om mijn VBA code te schrijven.
Momenteel heb ik een werkende code (gemaakt door de macro op te nemen), ik vind hem alleen nogal lang en on overzichtelijk.
Ik heb op internet verschillende stukken code gevonden waarin een "loop" zit verwerkt. Hiermee zou ik de code aanzienlijk kunnen verkleinen, maar krijg het niet werkend.
Ik hoop dat iemand op dit forum mij kan helpen.

In de bijlage heb ik twee voorbeeld bestandjes bijgevoegd.
Bestand 1 "proces bestand" is het bestand dat in onze organisatie wordt rond gestuurd en door verschillende mensen wordt gevuld en bewerkt. Het aantal regels is variabel (1 tot >500). Het zou kunnen dat een regel of cel wordt leeg gelaten.

Zodra het bestand is aangekomen bij de laatste persoon wordt op de "export" knop gedrukt. Bestand 2 "invoer bestand" wordt geopend vanaf een bepaalde locatie en wordt vervolgens gevuld met de informatie vanuit Bestand 1. Sommige kolomen worden niet meegekopieerd omdat ze niet meer van toepassing zijn, andere worden met elkaar vermenigvuldigd om additionele informatie toe te voegen.

De macro die is ingevoegd in bestand 1 ziet er alsvolgt uit:

Sub Voorbeeld()

    ChDir "C:\Documents and Settings\Bureaublad\test"
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Bureaublad\test\Invoer bestand.xlsx"
    
    Windows("Proces bestand.xlsx").Activate
    Range("A5:A13").Select
    Selection.Copy
    Windows("Invoer bestand.xlsx").Activate
    Range("A2").Select
    ActiveSheet.Paste
    
    Windows("Proces bestand.xlsx").Activate
    Range("B5:B13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Invoer bestand.xlsx").Activate
    Range("E2").Select
    ActiveSheet.Paste
    
    Windows("Proces bestand.xlsx").Activate
    Range("D5:D13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Invoer bestand.xlsx").Activate
    Range("B2").Select
    ActiveSheet.Paste
    
    Windows("Proces bestand.xlsx").Activate
    Range("E5:E13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Invoer bestand.xlsx").Activate
    Range("C2").Select
    ActiveSheet.Paste
    
    Range("D2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D10"), Type:=xlFillDefault
   
End Sub

de regels 5 t/m 13 worden in het voorbeeld gekopieerd, het zou ook kunnen dat het regel 5 t/m 500 moet zijn.

Ik hoop dat iemand mij kan helpen.
Alvast bedankt.

Maarten

 

[wampier]

Misschien eerst een proces vraag van mijn kant:

waarom niet een tweede hidden + protected tab in het eerste bestand die deze berekening meeneemt en die tab opslaan voor export?

In principe is wat je vraagt zeer eenvoudig, maar het lijkt onnodig complex voor wat je wilt?

Indien je de tweede tab linked met de eerste tab in de layout die je wil hebben, hoef je alleen maar tab 2 te saven als "invoer bestand.xlsx" en ben je direct klaar.

 

[MMellink]

Beste Wampier

Bedankt voor je reactie.
De twee bestanden zijn niet gelinkt omdat het basisbestand anders te groot wordt in omvang.
Ik probeer deze zo klein mogelijk te houden omdat het anders extern erg lang duurt voordat het bestand geladen is.

Daarnaast is het "proces bestand" niet altijd hetzelfde. De inhoud wordt aangepast nav. velden die mensen invullen.
Uiteindelijk zijn er vijf verschillende "proces bestanden" die geexporteerd dienen te worden naar één "invoerbestand".

Als voorbeeld heb ik maar één variant bijgevoegd, dit om mijn vraag makkelijker te kunnen stellen.

Maarten

 

[wampier]

Dat is duidelijk. Echter de uiteindelijke uitvoer moet altijd in hetzelfde template?

1) Hoe moet de VBA code worden ingericht dat er altijd dezelfde output uit komt?
2) is de formule (oppervlakte berekening) verplicht als formule of mag het antwoord ook direct in de cel?

De normale aanpak is bepalen hoeveel data er is en dan met een foreach overbrengen op de nieuwe file. daarvoor gebruik je references naar beide files en voorkom je zodoende onnodig veel switchen van files en eventueel het gebruik can copy/paste wat de snelheid niet ten goede komt.

 

[MMellink]

Nogmaals dank voor je reactie.

1) Er zijn vijf verschillende "proces bestanden" elk bestand heeft zijn eigen uniekecode die gebruikt wordt om data van het ene bestand in het andere te plaatsten. Het verschil is dusdanig minimaal dat ik alleen kolommen hoef te wijzigen (bijv. de macro in bestand 1 kopieert kolom A naar kolom C en de macro in bestand 2 kopieert kolom A naar kolom F).
Ik bedacht me dat ik hier geen hulp bij nodig heb omdat ik dat zelf kan. Maw één enkele code voldoet.

2) Het is prima als het antwoord direct in de cel komt. Ik heb alleen geen idee hoe ik dit moet doen.

Dat switchen kost inderdaad veel tijd en het ziet er ook nog eens niet uit. Het zou top zijn als daar een oplossing voor bestaat.

 

[wampier]

volgende copieerd kolom 1 naar kolom 1

  Dim huidig As Object
    Dim naartoe As Object
    Dim aantal As Long
    Dim count As Long
 
    Set huidig = ActiveWorkbook.Sheets(1)
    aantal = huidig.Range("a65000").End(xlUp).Row
    Workbooks.Open Filename:="C:\Documents and Settings\Bureaublad\test\Invoer bestand.xlsx"
    Set naartoe = ActiveWorkbook.Worksheets(1)
 
    count = 2
    For i = 5 To aantal
        naartoe.Cells(count, 1).Value = huidig.Cells(i, 1).Value
       'bovenstaande regel herhalen en aanpassen per gekopieerde kolom 
        count = count + 1
    Next i

dit is makkelijk aan te passen. de ",1" op de naartoe=huidig lijn is de kolom, in dit geval van 1 naar 1. deze regel is eenvoudig te copieren en dan vervolgens kolomnummers aanpassen.

naartoe.Cells(count, 1).Value = huidig.Cells(i, 2).Value * huidig.Cells(i, 3).Value

plaatst de uitkomst van de vermenigvuldiging van kolom 2 en 3 in kolom 1 van je target sheet. indien je de formule wenst kun je de formule in de value plaaten (gewoon copieren van je eigen macro)

 

[MMellink]

Briljant! :thumb:

Vanmorgen heb ik de code getest en alles werkt.
Dit is nog beter dan dat ik me had kunnen indenken. Niet alleen de code is een stuk verbeterd, ik snap ook een stuk meer van VBA.

Echt super bedankt voor je hulp Wampier! Top.