I have code that looks for a particular month and copies then pastes that same data in the same spot. This is bc the data changes to the next month every month and there are formulas that reference this other file. So instead of having to manually go though and copy paste values only I used the following code. But now I want to know if there's a way for excel to know what month it is and look for the previous month instead of me having to change the month in the code everytime.

Sub CopyMonth()
'Copy cells of cols C,D,G,H from rows containing "Significant" in
'col B of the active worksheet (source sheet) to cols
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Overtime Charts")

Dim sRow As Long 'row index on source worksheet
Dim sCount As Long
sCount = 0
sRow = 1


For sRow = 1 To Range("B2500").End(xlUp).Row
'use pattern matching to find "Apr" anywhere in cell
If Cells(sRow, "B") Like "*Apr*" Then
sCount = sCount + 1
'copy cols C,D,G,H
DestSheet.Cells(sRow, "C") = Cells(sRow, "C")
DestSheet.Cells(sRow, "D") = Cells(sRow, "D")
DestSheet.Cells(sRow, "G") = Cells(sRow, "G")
DestSheet.Cells(sRow, "H") = Cells(sRow, "H")

End If
Next sRow

MsgBox sCount & " Apr rows copied", vbInformation, "Transfer Done"

End Sub

So you want it to look for the current month you're in? or the one before the one you're in? If the former, try:



This post has been edited by ipisors: Apr 6 2012, 01:31 PM

And if you really wanted LAST MONTH, then instead of this line:

use this line:

Hi,
If today is in May, you would be changing



to

?

Ipisors beat me to it, you can also consider using a combination of the MonthName and Date functions

Thank you so much but I need it to look for the previouse month. So since we're in April right now I'd want it to say current month minus one so that'd it look for March.

Ok, so use the code I posted here

Oh and if you want to be really certain about the case, you can even enhance my code to convert to Proper, as in your original example.

So final would be:


This post has been edited by ipisors: Apr 6 2012, 01:40 PM

Worked thank you very much!

I'm glad it worked out !! good luck with your project -