VBA code adjustment to include only current month and previous month automatically.

**ElmerFud** · 08-03-2015, 04:55 PM

Need the code below to automatically include current month and previous month without having to change the code each month. Thanks!

Sub ImportServiceJobs()

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False

    Workbooks.Open Filename:="F:\Purchasing\SERVICE.xls"

    Dim LastRow As Long
    
    With Sheets("Master")
        LastRow = .Cells(Rows.Count, 2).End(xlUp).Row
        .Range("$A$4298:$F$14000").AutoFilter Field:=2, Criteria1:=">" & CStr(Date - Day(Date)), Operator:=xlAnd
        .Range("B2:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
            Workbooks("Test MATERIAL - TOOL REQUISITION FORM.xlsm").Worksheets("5 Digit Job List").Range("A2")
        .AutoFilterMode = False
    End With

    ActiveWorkbook.Close

    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
End With

End Sub

**jaslake** · 08-03-2015, 05:31 PM

Hi Elmer

Please wrap your Code in Code Tags.

**ElmerFud** · 08-04-2015, 08:19 AM

Originally Posted by jaslake

Hi Elmer

Please wrap your Code in Code Tags.

Sorry, I thought I did but one of the brackets was messed up.

**lexusap** · 08-04-2015, 08:46 AM

Hi Elmer,

have not tested yet on VBA, but you can use worksheetfunction.EoMonth.

Formula:



Dim LDate As String, a As String, b As String



LDate = Date



MsgBox LDate



a = WorksheetFunction.EoMonth(LDate, -2) + 1   'First Day of Previous Month

b = WorksheetFunction.EoMonth(LDate, 0)        ' Laste day of Current month

Let me know whether it work or not.

Cheers
Lex

**jaslake** · 08-04-2015, 12:09 PM

Hi Elmer

Not tested...try this.

.Range("$A$4298:$F$14000")..AutoFilter Field:=2, Operator:= _
            xlFilterValues, Criteria2:=Array(1, DateAdd("m", -1, Now), 1, Now)

**ElmerFud** · 08-05-2015, 12:45 PM

Originally Posted by jaslake

Hi Elmer

Not tested...try this.

.Range("$A$4298:$F$14000")..AutoFilter Field:=2, Operator:= _
            xlFilterValues, Criteria2:=Array(1, DateAdd("m", -1, Now), 1, Now)

Jaslake, That works great thanks! But for some reason it's not bringing over the right columns and rows for some reason. The range should be columns and rows pertaining to A thru F only. It's bringing over all columns and rows A thru I. Can you help with that?

**jaslake** · 08-05-2015, 01:00 PM

Hi Elmer

Not without seeing your Workbook and just what it is you're trying to do.

On the surface this line if Code makes no sense to me...your placing your Filter starting at Row 4298...

.Range("$A$4298:$F$14000")..AutoFilter Field:=2, Operator:= _
            xlFilterValues, Criteria2:=Array(1, DateAdd("m", -1, Now), 1, Now)

And yet you start the Copy of visible cells at Row 2...

.Range("B2:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy _

**ElmerFud** · 08-05-2015, 02:13 PM

Originally Posted by jaslake

Hi Elmer

Not without seeing your Workbook and just what it is you're trying to do.

On the surface this line if Code makes no sense to me...your placing your Filter starting at Row 4298...

.Range("$A$4298:$F$14000")..AutoFilter Field:=2, Operator:= _
            xlFilterValues, Criteria2:=Array(1, DateAdd("m", -1, Now), 1, Now)

And yet you start the Copy of visible cells at Row 2...

.Range("B2:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy _

Thanks again Jaslake, Yes, their demands here are never ending. Your right originally it transferred rows starting at ("$A$4298:$F$14000") to current but now their asking me to isolate automatically the most current month along with the last two years. So it's change yet again on me and I forgot to update the code and my question. What I'm trying to accomplish with my limited knowledge is to transfer Columns and Rows A thru F only for the current month and to also include the last two years.

**jaslake** · 08-05-2015, 03:02 PM

Hi Elmer

You've got my knickers twisted. Attach your File...I'll look at it...

**bakerman2** · 08-05-2015, 01:16 PM

How can this work correctly ? (2 points before Autofilter)

.Range("$A$4298:$F$14000")..AutoFilter Field:=2, Operator:= _
            xlFilterValues, Criteria2:=Array(1, DateAdd("m", -1, Now), 1, Now)

VBA code adjustment to include only current month and previous month automatically.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Re: VBA code adjustment to include only current month and previous month automatically.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula for amount on previous month based on current month

[SOLVED] "copy worksheet from previous month and rename to current month" modified?

Macro using vlookups comparing 2 months paysheet(previous month and current month)

[SOLVED] Date formula with text in same cell but using previous month not current month

[SOLVED] VBA to identify the current month and previous month based on system date

copy worksheet from previous month and rename to current month

automatically update chart plotting current month and previous 6

Bookmarks

Bookmarks

Posting Permissions