Home

How to copy the values in entire column after filter is applied

%3CLINGO-SUB%20id%3D%22lingo-sub-716989%22%20slang%3D%22en-US%22%3EHow%20to%20copy%20the%20values%20in%20entire%20column%20after%20filter%20is%20applied%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716989%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20a%20particular%20column%3CSPAN%20class%3D%22diff-add%22%3E%2C%20I%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eneed%20to%20filter%20for%20value%20and%20copy%20entire%20visible%20values%20in%20the%20column%20and%20paste%20it%20in%20another%20sheet.%3C%2FP%3E%3CP%3EI%20have%20used%20below%20code%20however%20it%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22diff-add%22%3Edoes%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Enot%20work%20when%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22diff-add%22%3EI%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ehave%20only%20one%20line%20excluding%20filter%20post%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22diff-add%22%3EI%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Efilter%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22diff-add%22%3EWorksheets(%22Sheet1%22).UsedRange.AutoFilter%20Field%3A%3D6%2C%20Criteria1%3A%3DWorksheets(%22Sheet2%22).Range(%22A%22%20%26amp%3B%20i).Value%0A%20%20%20%20%20%20Range(%22S1%22).Select%0A%20%20%20%20Range(%22S2%22%2C%20Cells(Rows.Count%2C%20%22S%22).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1%2C%201).Select%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlDown)).Select%0A%20%20%20%20Selection.Copy%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EChandrakanth.K%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-716989%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722217%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20copy%20the%20values%20in%20entire%20column%20after%20filter%20is%20applied%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722217%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152352%22%20target%3D%22_blank%22%3E%40Chandrakanth%20K%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20video%26nbsp%3Bon%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.exceltrainingvideos.com%2F%22%20target%3D%22_self%22%20rel%3D%22home%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExcel%20VBA%20Training%20Online%3C%2FA%3E%26nbsp%3Bthat%20has%20%3CSPAN%3Ecomplete%20VBA%20code%20to%20copy%20auto%20filtered%20data%20to%20another%20worksheet%20(%3CA%20href%3D%22http%3A%2F%2Fwww.exceltrainingvideos.com%2Fcopy-auto-filtered-data-to-another-worksheet-automatically-with-vba%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.exceltrainingvideos.com%2Fcopy-auto-filtered-data-to-another-worksheet-automatically-with-vba%2F%3C%2FA%3E).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20hope%20this%20helps!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chandrakanth K
Contributor

In a particular column, I need to filter for value and copy entire visible values in the column and paste it in another sheet.

I have used below code however it does not work when I have only one line excluding filter post I filter the data.

 

Worksheets("Sheet1").UsedRange.AutoFilter Field:=6, Criteria1:=Worksheets("Sheet2").Range("A" & i).Value
      Range("S1").Select
    Range("S2", Cells(Rows.Count, "S").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

Regards,

Chandrakanth.K 

1 Reply

Hello @Chandrakanth K,

 

There is a video on Excel VBA Training Online that has complete VBA code to copy auto filtered data to another worksheet (http://www.exceltrainingvideos.com/copy-auto-filtered-data-to-another-worksheet-automatically-with-v...).

 

I hope this helps!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies