vba help - autofilter and copy 3 visible columns

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
780
Office Version
  1. 2010
Platform
  1. Windows
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
Rich (BB code):
.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


VBA Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,002
Hello Mg,

Perhaps this:-

VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
780
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,002
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,525
Messages
5,548,556
Members
410,849
Latest member
DannyNg
Top