How do I subtract 2 days in VBA.

[thardin]

Here is a copy and paste VBA code I currently use at work.

However, I am trying to add an IF and THEN statement because on Mondays I have to open and copy from a file with Saturdays date (where highlighted) instead of today's current date.

How would you recommend doing this?

Thanks.

 

[thardin]

Sub Grab_OBSB158_Data()


' Grab_DOB_Data Macr


' Keyboard Shortcut: Ctrl+


Dim strYearShort As String, strMonthShort As String, strMonthLong As String, strDay As String, strYearLong As Strin

Dim strFullDate As Stri

Dim Drive As Stri

Dim strOpenPath As Stri

Dim strDestinationFileName As Stri

Dim OpenFile As Stri

Dim PasteFile As Stri



strYearShort = Format(Now, "yy

strYearLong = Format(Now, "yyyy

strMonthShort = Format(Now, "mm

strMonthLong = Format(Now, "mmmm

strDay = Format(Now, "DD

strFullDate = strYearLong & strMonthShort & strD

strMonthFolder = strMonthShort & "-" & strMonthLo

strFullDate2 = strMonthShort & "." & strDay & "." & strYearSho



'file location to open & gr

Drive = "X

strOpenPath = "\shareholder_accounting\529 C Share Restriction Controls\Fund Held\Delivered to Fund\EPM_output

strOpenPath = strOpenPath & strYearLong & "\" & strMonthFolder & "

OpenFile = "OBSB158_Delivered_to_Fund_" & strFullDate & ".xls



strOpenPath = Drive & strOpenPath & OpenFi



'Name of file to pas

PasteFile = strFullDate2 & " Del to Fund Violations.xls






'Open workboo

Workbooks.Open strOpenPa


'grab Data And Cop

Sheets("Sheet1").Activa

Range("A:N").Sele



Selection.Co



'spot to paste da

Workbooks(PasteFile).Activa

Worksheets("Violations").Activa

Range("A1").Sele


'paste dat

ActiveSheet.Pas



End S
ub tea
ctteteta py cttey
thk:
x"te le x"\"\":"ab rtngay")")")")") ngngngngngngg
'h
'o
'lations").Activate

Range("A1").Select



'paste data

ActiveSheet.Paste



End Sub

 

[steve the fish]

You must be desperate seen as you made 4 threads. Use this at the top of the procedure and then use d instead of now.

Dim d As Date

If Weekday(Date) = vbMonday Then
    d = Date - 2
Else
    d = Date
End If