vba code to give next Sunday date for pasted data

bh24524

Active Member
Joined
Dec 11, 2008
Messages
365
Office Version
  1. 365
  2. 2007
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:

VBA Code:
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:
1710421951313.png


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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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

Code:
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
 
Upvote 0
Solution
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:

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,809
Messages
6,181,076
Members
453,020
Latest member
mattg2448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top