vba help - autofilter and copy 3 visible columns

[Mallesh23]

Hi Team,

I want to autofilter data and copy 3 Non Contigious columns and paste into output sheet.
my below code works, but Can we shorten code here?...by copying all 3 columns one go ..

Something like

.Columns(1,3,10).Resize(.Rows.Count - 1).Offset(1).Copy

.Columns(1).Resize(.Rows.Count - 1).Offset(1).Copy
.Columns(3).Resize(.Rows.Count - 1).Offset(1).Copy
.Columns(10).Resize(.Rows.Count - 1).Offset(1).Copy

Sub Autofilter_3_Columns

Dim sht As Worksheet
Dim wbk As Workbook
Set wbk = Workbooks.Add
Set sht = wbk.ActiveSheet

With rng_Data
    .AutoFilter field:=10, Criteria1:="Account Not Opened"
     If .Columns(10).SpecialCells(12).Count > 1 Then
        .Columns(1).Resize(.Rows.Count - 1).Offset(1).Copy
        sht.Range("a2").PasteSpecial xlPasteAll
        .Columns(3).Resize(.Rows.Count - 1).Offset(1).Copy
        sht.Range("B2").PasteSpecial xlPasteAll
        .Columns(10).Resize(.Rows.Count - 1).Offset(1).Copy
        sht.Range("C2").PasteSpecial xlPasteAll
        .Cells.AutoFilter
    Else
        .Cells.AutoFilter
     End If
End With

Application.CutCopyMode False
end sub

Thanks
mg

 

[vcoolio]

Hello Mg,

Perhaps this:-

Option Explicit
Sub Autofilter_3_Columns()

        Dim sht As Worksheet
        Dim wbk As Workbook
        Set wbk = Workbooks.Add
        Set sht = wbk.ActiveSheet

Application.ScreenUpdating = False

        
        With rng_Data
                .AutoFilter 10, "Account Not Opened"
                Union(.Columns("A"), .Columns("C"), .Columns("J")).Offset(1).Copy
                sht.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
                .AutoFilter
        End With

sht.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Mallesh23]

Hi VCoolio,

Thanks for your help, it worked.

Below two lines works.
Union(.Columns(Col_Employee_No), .Columns(Col_Display_Name), .Columns(Col_Primary_Bank_Name)).Offset(1).Copy
Union(.Columns(Col_Employee_No), .Columns(Col_Display_Name), .Columns(Col_Primary_Bank_Name)).Offset(1).SpecialCells(12).Copy

Below line not working when I am use resize in the code. I am not sure resize is required.

Union(.Columns(Col_Employee_No), .Columns(Col_Display_Name), .Columns(Col_Primary_Bank_Name)).Offset(1).resize(rng_Data.rows.count-1).SpecialCells(12).Copy


Thanks
mg

 

[vcoolio]

Hello Mg,

You're welcome.

That's correct. You don't need to use resize in this case. It would also be simpler to stay with the column letters instead of their titles.

Cheerio,
vcoolio.