only copy certain columns and rows from filtered range

mjschukas · May 18th 2014

after i filter a range, I would like only copy the filtered range from columns F:J and only the rows after the header and the first row (i.e., not the first two)...?

presently i'm coping the whole filtered range and the code i'm using to filter (works well):

Code

Set rFilterHeadsar = Range("a1", Range("a1").End(xlToLeft))
With wSheetAR
    .AutoFilterMode = False
    rFilterHeadsar.AutoFilter
    rFilterHeadsar.AutoFilter Field:=1, Criteria1:="=" & strCriteriaAR
    Set toCopyRangeAR = rFilterHeadsar.Offset(1, 0).SpecialCells(xlCellTypeVisible)
End With


toCopyRangeAR.Copy Sheets("mm").Range("A25")  'here is where i would like to only copy in columns F:J, rows after first two

...

thank you.

Smallman · May 19th 2014

Re: only copy certain columns and rows from filtered range

Hi Mjschukas

Thefollowing should get you over the line.

Code

Option Explicit
Sub Testo()
Dim st As Long
Dim sh As Worksheet
Dim lr As Long
Dim str As String ' strCriteriaAR Sheet2 below = Sheets("mm")


Set sh = Sheet1 'wSheetAR sheet
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
str = "2"
sh.AutoFilterMode = False


Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "=" & str
Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(12).Cells(2, 1).Select
st = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).Cells(2, 1).Row
Range("A" & st & ":G" & lr).Copy Sheet2.[a25]
End Sub

Display More

I put some variables in and tested it to ensure it ran OK. If you want to do the same just put some data in Col A of a blank workbook and change the str variable to one of the filter criteria. I only copied from A to G, change to suit.

Take care

Smallman

mjschukas · May 20th 2014

Re: only copy certain columns and rows from filtered range

works like a charm....thank you!

here's my code:

Code

Dim st As Long
    Dim sh As Worksheet
    Dim lr As Long
    Dim str As String
     
    Set sh = ActiveSheet                                                    'copy filter range minus top two rows (header and first record)
        lr = sh.Range("A" & Rows.Count).End(xlUp).Row
            sh.AutoFilterMode = False
            Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "=" & str
            Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(12).Cells(2, 1).Select
            st = Range("F2", Range("F" & Rows.Count).End(xlUp)).SpecialCells(12).Cells(2, 1).Row
        Range("F" & st & ":J" & lr).Name = "abc"  'and then i paste later

Display More

thank you...

Smallman · May 21st 2014

Re: only copy certain columns and rows from filtered range

No Worries

Glad it is sorted. Catch you next time.

Smallman

mjschukas · May 21st 2014

Re: only copy certain columns and rows from filtered range

thank you again...

one more question, what do you think about using: Set sh = ActiveSheet vs naming the sheet explicitly
(e.g., Set sh = Worksheets("sheet2").UsedRange)...?

thank you.

**royUK** · May 21st 2014

Re: only copy certain columns and rows from filtered range

If you use Active|Sheet you will need to have the actual sheet that you have the data on active, if you refer to the sheet in the code then you won't.

This will not worrk anyway if sh is declared as a worksheet

Code

Set sh = Worksheets("sheet2").UsedRange)...?

only copy certain columns and rows from filtered range

Participate now!

Share