T
Tdp
I have the following code:
Private Sub CommandButton1_Click()
Dim LastRow As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRowNumber As Long
Dim wks As Worksheet
Application.DisplayAlerts = False
Application.EnableEvents = False
Set LastRow = Sheet2.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
Set LastRow = Sheet5.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox4.Text
LastRow.Offset(1, 2).Value = TextBox5.Text
MsgBox ("Data has been entered")
For Each wks In Worksheets(Array("customers", "customers2"))
With wks
FirstRow = 2 'headers in row 1
'Changed LastRow to LastRowNumber
'Dim LastRowNumber as Long added above
'LastRow is declared as Range
'We need it to be Long
LastRowNumber = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRowNumber Step 1
If Application.CountIf(.Range("a2").EntireColumn, _
.Cells(iRow, "A").Value) > 1 Then
'it's a duplicate
MsgBox .Cells(iRow, "A").Value
.Rows(iRow).Delete
End If
Next iRow
End With
Next wks
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
TextBox1 is where the names are entered, the rest are dates.
This is at the end of a code that saves the data to two sheets.
Sheet29customer) and sheet3(customer2). It works well in deleting any
duplicate names and leaving the lastest entry. The trouble I am having is
that on sheet3(customer2) it adds numbers to the name column("A") as well as
deleting any duplicates.
Any ideas
Private Sub CommandButton1_Click()
Dim LastRow As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRowNumber As Long
Dim wks As Worksheet
Application.DisplayAlerts = False
Application.EnableEvents = False
Set LastRow = Sheet2.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
Set LastRow = Sheet5.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox4.Text
LastRow.Offset(1, 2).Value = TextBox5.Text
MsgBox ("Data has been entered")
For Each wks In Worksheets(Array("customers", "customers2"))
With wks
FirstRow = 2 'headers in row 1
'Changed LastRow to LastRowNumber
'Dim LastRowNumber as Long added above
'LastRow is declared as Range
'We need it to be Long
LastRowNumber = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRowNumber Step 1
If Application.CountIf(.Range("a2").EntireColumn, _
.Cells(iRow, "A").Value) > 1 Then
'it's a duplicate
MsgBox .Cells(iRow, "A").Value
.Rows(iRow).Delete
End If
Next iRow
End With
Next wks
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
TextBox1 is where the names are entered, the rest are dates.
This is at the end of a code that saves the data to two sheets.
Sheet29customer) and sheet3(customer2). It works well in deleting any
duplicate names and leaving the lastest entry. The trouble I am having is
that on sheet3(customer2) it adds numbers to the name column("A") as well as
deleting any duplicates.
Any ideas