Change VBA code to report last month i.e. May as opposed to this month i.e. June in file n

[robertguy]

Hi,

Can anyone please advise me of the syntax I need to amend the following VBA/macro code.

The code current does exactly what it is supposed to do i.e. save the file in the current path of the file, and save it as ‘Monthly Projects Within Schedule Report (June 2018)’.xlsx', however, as the report’s data is for the previous month, I need the code to be amended so it doesn’t report the current month but the previous month e..g ‘Monthly Projects Within Schedule Report (May 2018)’.xlsx',

Please Login or Register  to view this content.

ActiveWorkbook.SaveAs Filename:=srcWB.Path & "\Monthly Projects Within Schedule Report (" & Format(Date, "MMMM-yyyy") & ").xlsx", FileFormat:=51

Please Login or Register  to view this content.

Any assistance in this matter would be greatly appreciated

Many thanks in advance

Regards


Rob

[JLGWhiz]

Please Login or Register  to view this content.

subtract 28 days from the current date as shown above.

[robertguy]

JLGWhiz

EXCELLENT !!

Many thanks for your assistance


Thanks Again


Regards


Rob

[jindon]

Are you sure about subtracting 28 days?

If the date is 31 st of May, it still May, so should be

Please Login or Register  to view this content.

[JLGWhiz]

Are you sure about subtracting 28 days?

If the date is 31 st of May, it still May, so should be

Please Login or Register  to view this content.

If you subtract more than 28 days it could put March 1st back into January. It was assumed that the report will be initiated sometime within the first week of the new month. Any later would have people working from old data which could be obsolete and misleading. But your suggestion would work well for most months.

[Swithim]

many thanks for the answers i found here as it is super helpful for me. was wondering if i may ask questions in case i would have some? i'm just a newbie so i don't know... thanks

[jindon]

Swithim,

Yes you can, but not in this thread.
You need to open a new thread for your own question.