ah2024_2024
New Member
- Joined
- Jun 18, 2024
- Messages
- 14
- Office Version
- 365
- Platform
- Windows
MY code below works great, but a flaw I realized is that the source sheet isn't going to be in the same order each day which means the data my teams enters in certain columns will not remain with the correct name in column A. Essentially I am needing my paste to occur at after the last name in column A, but then delete duplicates from the new set of data. Here is what's happening. Sheet 3 is my source sheet and if certain criteria is meet then column d is copied and then pasted into my target sheet starting in A9. Again, works great, but I need all the pasted names to STAY in the order they are in and essentially just give me NEW names ONLY when the paste happens.
I'm hoping to not have to change much of this because I'm just starting out and this works and I'm afraid changing it means I won't understand enough to make it happen correctly. HELP!
CURRENT CODE THAT WORKS to copy/paste but copy and pastes over each time.
Sub MOVE_FTW_2()
Application.ScreenUpdating = False
Dim lastRow As Long
Dim c As Range
Dim dtToday As Date
Dim TermDate As Range
Dim v As Variant
Dim source As Worksheet
Dim target As Worksheet
Set wssource = Worksheets("Sheet3")
Set wstarget = Worksheets("FTW")
If Not wssource.AutoFilterMode Then
wssource.Range("A1
1").AutoFilter
End If
wssource.Activate
wssource.Range("a1" & lr).AutoFilter Field:=6, Criteria1:="fort worth"
wssource.Range("a1" & lr).AutoFilter Field:=13, Criteria1:=Array("="), _
Operator:=xlFilterValues, Criteria2:=Array(1, Format(Now(), "MM/DD/YYYY"))
wssource.Range("a1" & lr).AutoFilter Field:=9, Criteria1:="REP"
wssource.Range("d2:d20000").SpecialCells(xlCellTypeVisible).Copy
wstarget.Range("A9").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = 0
Application.ScreenUpdating = True
Sheets("ftw").Range("A8:a100").RemoveDuplicates Columns:=Array(1), Header:=xlYes
wssource.Activate
ActiveSheet.ShowAllData
wstarget.Activate
Application.ScreenUpdating = True
End Sub
I'm hoping to not have to change much of this because I'm just starting out and this works and I'm afraid changing it means I won't understand enough to make it happen correctly. HELP!
CURRENT CODE THAT WORKS to copy/paste but copy and pastes over each time.
Sub MOVE_FTW_2()
Application.ScreenUpdating = False
Dim lastRow As Long
Dim c As Range
Dim dtToday As Date
Dim TermDate As Range
Dim v As Variant
Dim source As Worksheet
Dim target As Worksheet
Set wssource = Worksheets("Sheet3")
Set wstarget = Worksheets("FTW")
If Not wssource.AutoFilterMode Then
wssource.Range("A1
End If
wssource.Activate
wssource.Range("a1" & lr).AutoFilter Field:=6, Criteria1:="fort worth"
wssource.Range("a1" & lr).AutoFilter Field:=13, Criteria1:=Array("="), _
Operator:=xlFilterValues, Criteria2:=Array(1, Format(Now(), "MM/DD/YYYY"))
wssource.Range("a1" & lr).AutoFilter Field:=9, Criteria1:="REP"
wssource.Range("d2:d20000").SpecialCells(xlCellTypeVisible).Copy
wstarget.Range("A9").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = 0
Application.ScreenUpdating = True
Sheets("ftw").Range("A8:a100").RemoveDuplicates Columns:=Array(1), Header:=xlYes
wssource.Activate
ActiveSheet.ShowAllData
wstarget.Activate
Application.ScreenUpdating = True
End Sub