brankscaffold
New Member
- Joined
- Jun 15, 2022
- Messages
- 18
- Office Version
- 365
- Platform
- Windows
This code works well.
copy and create new sheet with name and year. this is good as it should be.
but if I run this code again after adding new data I get all duplicates
a check must also be carried out on the existing sheets before they are copied to this sheet.
Can anyone help me with this code.
because I can't figure it out
copy and create new sheet with name and year. this is good as it should be.
but if I run this code again after adding new data I get all duplicates
a check must also be carried out on the existing sheets before they are copied to this sheet.
Can anyone help me with this code.
because I can't figure it out
VBA Code:
Sub Inboekingen_kopieren()
Const FirstRow = 5
Dim rij As Long
Dim n As Long
Dim src As Worksheet
Dim trg As Worksheet
Dim OKToCopy As String
Set src = Sheets("Jaar 2022")
Application.ScreenUpdating = False
For n = FirstRow To src.Cells(Rows.Count, "AE").End(xlUp).Row
OKToCopy = "N"
Set trg = Nothing
If src.Cells(n, "AE").Value <> "" Then
If n = FirstRow Or _
src.Cells(n, "B").Value <> src.Cells(n - 1, "B").Value Or _
src.Cells(n, "E").Value <> src.Cells(n - 1, "E").Value Or _
src.Cells(n, "H").Value <> src.Cells(n - 1, "H").Value Or _
src.Cells(n, "AD").Value <> src.Cells(n - 1, "AD").Value Then
OKToCopy = "Y"
Else
OKToCopy = "N"
End If
If OKToCopy = "Y" Then
On Error Resume Next
Set trg = Worksheets(src.Cells(n, "AE").Value & " 22")
If Err <> 0 Then
Err.Clear
Set trg = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
trg.Name = src.Cells(n, "AE").Value & " 22"
With src
.Range(.Cells(1, "A"), .Cells(4, "AZ")).Copy
End With
trg.Cells(1, "A").PasteSpecial
End If
rij = trg.Cells(Rows.Count, "AE").End(xlUp).Row + 1
With src
.Range(.Cells(n, "A"), .Cells(n, "AZ")).Copy
End With
trg.Cells(rij, "A").PasteSpecial
End If
End If
Next n
On Error GoTo 0
Application.ScreenUpdating = True
End Sub