Auto filter copy macro - date based data

[chrislp]

I'm a VB novice and am struggling to find the right code to copy, from one workbook, and paste into another, a defined range (only 15 rows) of auto filtered data, based on it being within a defined date range (I have a forumula which gives me a marker (1) for filtering. The row data changes according to the date and I want to copy no more than 15 rows of the filtered data. How would I code this to make the copy of the filtered selection within a defined range (B3:M250)? This is part of the code I currently have:

Windows("3.Period 2_Milestone_matrix.xls").Activate
    Sheets("All Milestones").Select
    Selection.AutoFilter Field:=9, Criteria1:="=CER", Operator:=xlAnd
    Selection.AutoFilter Field:=18, Criteria1:="=1", Operator:=xlAnd
    Range("B3:M250").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Highlight report template.xls").Activate
    ActiveSheet.Paste

Hope this is clear, many thanks.

 

[texasalynn]

Welcome to MrExcel board...

try this

Windows("3.Period 2_Milestone_matrix.xls").Activate
Sheets("All Milestones").Select
Selection.AutoFilter Field:=9, Criteria1:="=CER", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=1", Operator:=xlAnd
LR = Cells(65536, 2).End(xlUp).Row
    If LR > 1 Then
        Range("B3:M" & LR).SpecialCells(xlCellTypeVisible).Copy
    End If
Windows("Highlight report template.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False

 

[chrislp]

Thanks for the support texasalynn, i'll give it a go.

 

[chrislp]

Tried the above code and all is working fine when there are filter results. When there are no filter results however the copy seems to take the header row in row 2 and paste that. I'm not sure why it is selecting row 2 to copy, any ideas? Many thanks, here is the piece of code...

Sheets("ESRP All Milestones").Select
Selection.AutoFilter Field:=9, Criteria1:="=P&L", Operator:=xlOr, _
Criteria2:="=MCI"
Selection.AutoFilter Field:=18, Criteria1:="=1", Operator:=xlAnd
LR = Cells(65536, 3).End(xlUp).Row
If LR > 1 Then
Range("B3:M" & LR).SpecialCells(xlCellTypeVisible).Copy
End If
Windows("Highlight report template.xls").Activate
ActiveSheet.Paste

 

[texasalynn]

so your headers are in row 2? If so then change this

If LR > 1 Then
to this
If LR > 2 Then

 

[chrislp]

Perfect, it doesn't copy the header row, what is does do though is crash when it tries to paste and it hasn't copied any data. Is there a way of fixing this? Thanks

 

[texasalynn]

move the end if

Windows("3.Period 2_Milestone_matrix.xls").Activate
Sheets("All Milestones").Select
Selection.AutoFilter Field:=9, Criteria1:="=CER", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=1", Operator:=xlAnd
LR = Cells(65536, 2).End(xlUp).Row
If LR > 1 Then
Range("B3:M" & LR).SpecialCells(xlCellTypeVisible).Copy
Windows("Highlight report template.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End If

 

[chrislp]

Perfect! Thanks very much, you have helped me out of a hole there! Cheers.