Ik heb onderstaande code(niet het mooiste maar het werkt) waarin na het opslaan van een kopie bestand, bepaalde cellen van mijn werkblad gewist worden in het origineel.
Tot G24:H24 worden alle aangeduide cellen gewist en de cellen daaronder blijven gevuld.
Kan iemand mij in de juiste richting helpen?
Tot G24:H24 worden alle aangeduide cellen gewist en de cellen daaronder blijven gevuld.
Kan iemand mij in de juiste richting helpen?
Code:
Public Sub OpslaanKlacht()
Dim lRegel As Long
If Range("B6") = "" Then MsgBox "geen klachtnr", vbCritical: End
'Wegschrijven van de klacht in de tabel.
With Sheets("Overzicht 2023")
On Error Resume Next
lRegel = WorksheetFunction.Match(Range("B6"), .Range("A:A"), 0)
If lRegel = 0 Then lRegel = .Range("A" & .Rows.Count).End(xlUp).Offset(1).Row
lRegel = lRegel - 1
.Range("A1").Offset(lRegel, 0) = Range("B6")
.Range("A1").Offset(lRegel, 1) = Range("G9")
.Range("A1").Offset(lRegel, 2) = Range("G7")
.Range("A1").Offset(lRegel, 3) = Range("G10")
.Range("A1").Offset(lRegel, 4) = Range("B9")
.Range("A1").Offset(lRegel, 5) = Range("B10")
.Range("A1").Offset(lRegel, 6) = Range("B4")
.Range("A1").Offset(lRegel, 7) = Range("A13")
.Range("A1").Offset(lRegel, 8) = Range("B7")
.Range("A1").Offset(lRegel, 9) = Range("G35")
End With
'Opslaan klachtenformulier,
Sheets("Klachtformulier").Copy
ActiveWorkbook.SaveAs Filename:= _
"https://pcborotterdamzuid.sharepoint.com/sites/PCBOTechnischeDienst/Gedeelde%20documenten/6%20-%20Gemeente/2%20-%20Schade-Vandalisme-Lekkage/schademelding%20Formulier " & Range("G9").Value & " " & Range("G10").Value & ".xlsx"
ActiveWindow.Close
'Leeg maken klachtenformulier
ShonenKlachtenformulier
MsgBox "Gegevens opgeslagen.", vbInformation, "Klaar"
End Sub
Public Sub ShonenKlachtenformulier()
ActiveSheet.Range("B6:D6").ClearContents
ActiveSheet.Range("G6:H6").ClearContents
ActiveSheet.Range("G10:H10").ClearContents
ActiveSheet.Range("A13:H13").ClearContents
ActiveSheet.Range("A15:H15").ClearContents
ActiveSheet.Range("B17:D17").ClearContents
ActiveSheet.Range("G17:H17").ClearContents
ActiveSheet.Range("B18:D18").ClearContents
ActiveSheet.Range("G18:H18").ClearContents
ActiveSheet.Range("B19:D19").ClearContents
ActiveSheet.Range("G19:H19").ClearContents
ActiveSheet.Range("B20:D20").ClearContents
ActiveSheet.Range("G20:H20").ClearContents
ActiveSheet.Range("B21:D21").ClearContents
ActiveSheet.Range("G21:H21").ClearContents
ActiveSheet.Range("B22:D22").ClearContents
ActiveSheet.Range("G22:H22").ClearContents
ActiveSheet.Range("B23:D23").ClearContents
ActiveSheet.Range("G23:H23").ClearContents
ActiveSheet.Range("B24:D24").ClearContents
ActiveSheet.Range("G24:H24").ClearContents
ActiveSheet.Range("B25:D25").ClearContents
ActiveSheet.Range("G25:H25").ClearContents
ActiveSheet.Range("B27:D27").ClearContents
ActiveSheet.Range("G27:H27").ClearContents
ActiveSheet.Range("B28:D28").ClearContents
ActiveSheet.Range("G28:H28").ClearContents
ActiveSheet.Range("B29:D29").ClearContents
ActiveSheet.Range("G29:H29").ClearContents
ActiveSheet.Range("B30:D30").ClearContents
ActiveSheet.Range("G30:H30").ClearContents
ActiveSheet.Range("B31:D31").ClearContents
ActiveSheet.Range("G31:H31").ClearContents
ActiveSheet.Range("B32:D32").ClearContents
ActiveSheet.Range("G32:H32").ClearContents
ActiveSheet.Range("B33:D33").ClearContents
ActiveSheet.Range("G33:H33").ClearContents
For nTeller = 1 To 28
ActiveSheet.CheckBoxes(nTeller).Value = 0
Next
Blad2.ComboBox1.Enabled = True
End Sub