wdg1
03-07-2022, 04:04 AM
So, I have worksheet 1 ("Start"), with a dropdown list of 15 articles.
When 1 article is chosen, the other fiels indicate:
article-name, number, price, VAT and total.
Now, I want to copy this data into a second worksheet ("list"),
where the data must be copied into columns, depending the article choice.
When (worksheet 1, article "1") copy to (worksheet 2, row 1)
When (worksheet 1, article "2") copy to (worksheet 2, row 2)
When (worksheet 1, article "1") copy to (worksheet 2, row 3)
...
The macro works fine, but the VBA code seems te be "long" (8 lines for 1 choice).
Can that code be compressed?
Sub verwerk()
Dim cell As Range
Sheets("Start").Select
If Range("B8").Value = 2 Then _
Sheets("list").Select
Range("B2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 2) = Range("Start!subtot")
Sheets("Start").Select
If Range("B8").Value = 3 Then _
Sheets("list").Select
Range("C2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 3) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 4 Then _
Sheets("list").Select
Range("D2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 4) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 5 Then _
Sheets("list").Select
Range("E2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 5) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 6 Then _
Sheets("list").Select
Range("F2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 6) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 7 Then _
Sheets("list").Select
Range("G2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 7) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 8 Then _
Sheets("list").Select
Range("H2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 8) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 9 Then _
Sheets("list").Select
Range("C2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 9) = Range("Start!subtot")
Sheets("Start").Select
' end so further to article 15
End Sub
Thanks.2947229472
When 1 article is chosen, the other fiels indicate:
article-name, number, price, VAT and total.
Now, I want to copy this data into a second worksheet ("list"),
where the data must be copied into columns, depending the article choice.
When (worksheet 1, article "1") copy to (worksheet 2, row 1)
When (worksheet 1, article "2") copy to (worksheet 2, row 2)
When (worksheet 1, article "1") copy to (worksheet 2, row 3)
...
The macro works fine, but the VBA code seems te be "long" (8 lines for 1 choice).
Can that code be compressed?
Sub verwerk()
Dim cell As Range
Sheets("Start").Select
If Range("B8").Value = 2 Then _
Sheets("list").Select
Range("B2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 2) = Range("Start!subtot")
Sheets("Start").Select
If Range("B8").Value = 3 Then _
Sheets("list").Select
Range("C2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 3) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 4 Then _
Sheets("list").Select
Range("D2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 4) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 5 Then _
Sheets("list").Select
Range("E2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 5) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 6 Then _
Sheets("list").Select
Range("F2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 6) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 7 Then _
Sheets("list").Select
Range("G2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 7) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 8 Then _
Sheets("list").Select
Range("H2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 8) = Range("Start!subtot")
Sheets("Start").Select
Sheets("Start").Select
If Range("B8").Value = 9 Then _
Sheets("list").Select
Range("C2").Select 'deze cel moet gevuld zijn!
Selection.End(xlDown).Select
rij = 1 + ActiveCell.Row
ActiveSheet.Unprotect
Cells(rij, 9) = Range("Start!subtot")
Sheets("Start").Select
' end so further to article 15
End Sub
Thanks.2947229472