Hello,
In the process of building this userform for tracking purposes here at work. I have a sheet containing 6 columns. Basically I needed to code for the module to copy any rows from sheet 1 to sheet 2 if column 2 contained "Operations" or to sheet 3 if it contains "Scheduling". This works perfectly fine, however everytime the module is ran, it will duplicate the entries. Is there a code that it will detect any duplicates. I am new to coding so in all honesty I've built this from a grip of your guys's post lol. Forgive the randomness ive tried multiple ways and didn't delete anything just in case. And since it works now I haven't removed anything unnecessary.
Thank you in advance for any assistance..
Code
Sub Update() Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
LastColnum = 6
TestcolNum = 2
' Change worksheet designations as needed
'Set Source = ActiveWorkbook.Worksheets("Sheet1")
'Set Target = ActiveWorkbook.Worksheets("Sheet2")
'j = 2 ' Start copying to row 1 in target sheet
'For Each c In Source.Range("$B1:$B1000")
' Do 1000 rows
' If c = "Operations" Then
'Source.Rows(c.Row).Copy Target.Rows(j)
'j = j + 1
'End If
'Next c
Sheets(1).Activate
'MsgBox Cells(1, 1).End(xlDown).Row + 1
Cells(1, 1).Select
rowscount = Selection.CurrentRegion.Rows.Count
'MsgBox rowscount
For rowsnum = 2 To rowscount
Sheets(1).Activate
If UCase(Cells(rowsnum, TestcolNum)) = "OPERATIONS" Then
'MsgBox rowsnum
Range(Cells(rowsnum, 1), Cells(rowsnum, LastColnum)).Select
Selection.Copy
Sheets(2).Activate
If Cells(2, 1) = "" Then
Cells(2, 1).Select
ActiveSheet.Paste
Else
Cells(2, 1).Select
recordscount = Selection.CurrentRegion.Rows.Count
Cells(recordscount + 1, 1).Select
ActiveSheet.Paste
End If
End If
Next rowsnum
For rowsnum = 2 To rowscount
Sheets(1).Activate
If UCase(Cells(rowsnum, TestcolNum)) = "SCHEDULING" Then
Range(Cells(rowsnum, 1), Cells(rowsnum, LastColnum)).Select
Selection.Copy
Sheets(3).Activate
If Cells(2, 1) = "" Then
Cells(2, 1).Select
ActiveSheet.Paste
Else
Cells(2, 1).Select
recordscount = Selection.CurrentRegion.Rows.Count
Cells(recordscount + 1, 1).Select
ActiveSheet.Paste
End If
End If
Next rowsnum
End Sub
Display More