vba code to give next Sunday date for pasted data

[bh24524]

Hello, I have some code I am working through to more automate a weekly process of keeping a record of different jobs held by employees. This is the code I have so far and I will explain below what it is doing:

Sub Macro1()
'

    Range("A4:L4").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$4:$L$266").AutoFilter Field:=5, Criteria1:=Array( _
        "G", "D", "P"), Operator:= _
        xlFilterValues
    Union(Range("A5:B5", Range("B5").End(xlDown)), Range("L5", Range("L5").End(xlDown))).Copy
    Sheets("Bid History").Select
    Range("F3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.Wait (Now + TimeValue("0:00:05"))
   Range("J3", Range("L3").End(xlDown)).Copy
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub

Background of the sheet
1. On our first tab, we are creating a filter after we have entered the jobs people are changing to for the coming week so that we exclude the ones that are irrelevant.
2. We are then copying data in columns A, B, and L down to the last blank row of that tab.
3. We then paste this data into a bid history tab in F3. This pasted data spans to column H on that tab.
4. Any jobs never held by employees appear in columns J thru L which are formulas comparing to the ongoing master list contained in Columns A thru C. This info is pasted as values to the last empty cell in columns A thru C.

What I am looking to do
For every name and info that is pasted over in columns A thru C - our master list, I want column D to have next Sunday's date for those newly pasted names - it should always be the next upcoming Sunday. So as a sample:

In this scenario, I had two names populate over in columns J thru L and that data was then pasted to column A to add to the master list. I would like the macro to put in 3/17/2024 in column D for both of them since that is the next upcoming Sunday date. I have more that I want the code to do after that, but I can take care of that myself no issue. It's just the Sunday date in the number of newly pasted cells which I am still sketchy on. What line of coding could do this? Thank you for any help and time on this.

 

[ranman256]

if you put this code into a module
then add the macro PastewDate onto the QUICK ACCESS TOOLBAR
then

1. copy your block
2. click PastewDate icon on the toolbar (to Paste)
done

Sub PastewDate()
ActiveSheet.Paste
Selection.Columns(4).Value = getSunDate()
Application.CutCopyMode = False
End Sub


Private Function getSunDate()
Dim vDat, vToday
Dim i As Integer

vToday = Date
i = Format(vToday, "w")
vDat = DateAdd("d", -(i - 8), vToday)
'Debug.Print vDat
getSunDate = vDat
End Function

 

[bh24524]

Thank you, Ranman. I did do a slightly different approach where I used your solution. It turns out I was actually able to eliminate several steps in the process and the Sunday date function rendered them unnecessary. All in all I ended up with:

Sub Update_Bid_History()

    Range("A4:L4").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$4:$L$266").AutoFilter Field:=5, Criteria1:=Array( _
        "G", "D", "P"), Operator:= _
        xlFilterValues
    Union(Range("A5:B5", Range("B5").End(xlDown)), Range("L5", Range("L5").End(xlDown))).Copy
    Sheets("Bid History").Select
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Selection.Columns(4).Value = getSunDate()
    Application.CutCopyMode = False
    Columns("A:D").Select
    ActiveWorkbook.Worksheets("Bid History").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Bid History").Sort.SortFields.Add2 Key:=Range( _
        "B2", Range("B2").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Bid History").Sort
        .SetRange Range("A1", Range("D1").End(xlDown))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("A1", Range("D1").End(xlDown)).RemoveDuplicates Columns:=Array(2, 3), _
        Header:=xlYes
    Sheets("NEW BID").Select
    Range("A4:L4").Select
    Selection.AutoFilter
End Sub

Private Function getSunDate()
Dim vDat, vToday
Dim i As Integer

vToday = Date
i = Format(vToday, "w")
vDat = DateAdd("d", -(i - 8), vToday)
'Debug.Print vDat
getSunDate = vDat
End Function

So thanks for the help with that function. I'll be keeping that one handy as there are times I need next Sunday's date in other applications, so perhaps it could be of use in those.