Een dynamische range kopieren met VBA

[dvk50]

Dag allemaal,

Ik ben bezig met een (voor mij althans) kleine breinkraker (omdat ik geen volleerd VBA programmeur ben)

Ik heb een workbook met 3 sheets.
In sheet 1 kan de gebruiker (een call center medewerker) met behulp van 3 relevante cellen een overzicht van een betalingsregeling opstellen.
Belangrijkste input, totaal bedrag, aantal termijnen en begindatum. So far so easy. In sheet 1 wordt vervolgens een overzicht opgesteld van alle termijnen met eindatum, betalingskenmerk etc. That was easy
Het aantal termijnen kan variëren tussen 5 en 150
In sheet 2 worden die termijnregels vertaald naar een tabel in het juiste format om later als CSV bestand te kunnen uploaden naar een financieel pakket. De tabel is 150 regels lang omdat het maximaal aantal termijnen is wat in sheet 1 kan worden ingevuld en uitgerekend.
In sheet 3 worden de regels van meerdere betalingsregelingen verzameld die via sheet 1 worden ingevoerd en vanuit sheet 2 worden gekopieerd (paste-value)

Dat kopiëren gebeurt middels een macro die gekoppeld is aan een button in sheet 1.
Ik heb dat nu opgelost door een vaste range (B2:X151) van sheet 2 naar sheet 3 te laten kopiëren onderaan de tabel. Bij elke actie wordt de lijst in sheet 3 dus aangevuld met 150 regels uit sheet 2. Ook als de regeling maar 10 termijnen is. Dan zijn er dus 140 lege regels. Geen fraaie oplossing maar die lege regels kan ik voor de export naar het financiële systeem wel wegfilteren.

Maar daar wil ik vanaf. Ik zoek dus een manier om met die macro / VBA alleen het aantal regels van de afgesproken regeling te laten kopiëren. Dus op basis van de input in sheet 1 met het aantal termijnen.
Ik heb al een flinke tijd op allerlei fora gezocht (ook Engels) maar heb nog niks gevonden wat in de buurt komt, of ik begrijp het niet.
Misschien zoek ik wel op de verkeerde termen.

Het stukje code wat ik nu gebruik is als volgt:

Sheets("JP").Select 'sheet2
Range("B2:X151").Select
Selection.Copy
Sheets("upload").Select 'sheet3
Range("A1").Select
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Maar in plaats van B2:X151 wil ik dus het liefst "B2:X(aantal_termijnen_uit_sheet_1)"

Heeft iemand dit al eens bij de hand gehad of ergens gezien, met een verwijzing ben ik ook al geholpen.
Zoals gezegd, ik ben geen VBA expert maar start meestal met het opnemen van een macro en probeer die dan in de VBA editor aan te passen.

Op mezelf kom ik op die manier niet verder nu.
En als ik deze template overdraag aan anderen wil ik liever zo veel mogelijk overbodige acties er uit hebben.

groet,

Diederik

 

[edmoor]

Bepaal het aantal termijnen en zet deze in een variabele, bijvoorbeeld de variabele Termijnen die je als volgt declareert:
Dim Termijnen As Long

De Select opdracht wordt dan:
Range("B2:X" & Termijnen).Select

Al wordt het gebruik van .Select niet aangeraden, maar dat was je vraag niet.

 

[HSV]

Uit de losse pols, dus test het maar eens Diederik.

with Sheets("JP")
        Sheets("upload").cells(rows.count,1).end(xlup).offset(1).resize(.range("b2:b" & .cells(rows.count,2).end(xlup).row).rows.count, 23) = .range("b2:x" & .cells(rows.count,2).end(xlup).row).value
end with

 

[edmoor]

Dat bedoelde ik dus met mijn laatste opmerking

 

[dvk50]

nu al reacties

Dank voor deze 2 aanwijzingen.

Die van Ed spreekt me het meest aan omdat ik denk dat ik die begrijp.
Alleen heb ik dan nog 1 vraag, hoe krijg ik de waarde (aantal termijnen) in de variabele "termijnen"
Die waarde staat bijvoorbeeld in het invoerveld in sheet 1 in cel C8

Maar je geeft ook aan dat de optie "select" beter niet gebruikt kan worden, why is that?

De oplossing van Harry kan ik nog niet helemaal plaatsen. Ik ben wat dat betreft handiger met worksheet functies dan met VBA

groet
Diederik

 

[edmoor]

Plaats je document eens, of een voorbeeld er van.

 

[dvk50]

voorbeeldbestand

bij deze het bestand, de bewust macro huist onder de knop <copy JP>

mmm, probeer het maar zowel de xlsx als een zip krijg ik niet onder de 100Kb.
Is er een andere manier?

Heb hem net wetransfer geupload. beschikbaar op http://we.tl/WA5aHz34wj
(weet niet of dat mag van forumregels?)

 

[Warme bakkertje]

Of

Sheets("upload").Cells(Rows.Count, 1).End(xlUp).Offset(1) _
        .Resize(Sheets(1).Range("C8").Value, 23).Value =Sheets("JP") .Range("B2:X" & Sheets(1).Range("C8").Value).Value

ofwel

Dim Termijnen As Long

Termijnen = Sheets(1).Range("C8").Value

Sheets("JP").Range("B2:X" & Termijnen).Copy
Sheets("upload").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Termijnen, 23).PasteSpecial xlPasteValues

 

[dvk50]

Merci

Dank voor alle reacties.
Rudi, jouw laatste post heeft me over het randje geholpen. Ik ga beide opties vandaag eens proberen.

Dank jullie, laat het weten als het gelukt is.

groet,

Diederik

 

[dvk50]

Dag allemaal, nogmaals dank voor jullie hulp, beide oplossingen werken. Ik heb voor de 2e gekozen in de laatste post van Rudi. Vervolgens nog wat checks ingebouwd. OA een msgbox met een controleverslag van de boeking voordat de journaalregels doorgeboekt worden naar de verzamelstaat. Werkt allemaal als een zonnetje en scheelt de collega's hier veel tijd in het verwerken van betalingsregelingen.

 

[dvk50]

Dag allemaal, nogmaals dank voor jullie hulp, beide oplossingen werken. Ik heb voor de 2e gekozen in de laatste post van Rudi die ook al eerder genoemd werd. Vervolgens nog wat checks ingebouwd. OA een msgbox met een controleverslag van de boeking voordat de journaalregels doorgeboekt worden naar de verzamelstaat. Werkt allemaal als een zonnetje en scheelt de collega's hier veel tijd in het verwerken van betalingsregelingen.

 

[snb]

In sheet 2 worden die termijnregels vertaald naar een tabel in het juiste format om later als CSV bestand te kunnen uploaden naar een financieel pakket.

Dan lijkt het me voor de hand liggender al deze Excelstappen over te slaan en rechtreeks naar het csv bestand te schrijven.

Sub M_snb()
   CreateObject("scripting.filesystemobject").opentextfile("G:\OF\nieuw.csv", 8, True).write
End Sub