Set Default to Copy and Paste Visible Cells Only

%3CLINGO-SUB%20id%3D%22lingo-sub-132326%22%20slang%3D%22en-US%22%3ESet%20Default%20to%20Copy%20and%20Paste%20Visible%20Cells%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-132326%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20anyone%20know%20if%20we%20can%20set%20the%20Copy%20and%20Paste%20Function%20to%20default%20into%20copying%20and%20pasting%20only%20the%20visible%20cells%3F%20I'm%20using%20Excel%202016%20in%20Windows%2010.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20been%20doing%20the%20Alt%20%2B%20%3B%20and%20then%20copying%20it%20way%20but%20when%20copying%2C%20I%20never%20want%20the%20hidden%20cells%20anyway.%20So%20I'm%20wondering%20if%20this%20could%20be%20set%20as%20default%20to%20avoid%20the%20extra%20step.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-132326%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECopy%20and%20Paste%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ecopying%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%202016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20PC%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-132370%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20Default%20to%20Copy%20and%20Paste%20Visible%20Cells%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-132370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EHi%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3EThere%20is%20no%20way%20to%20change%20the%20default%20action%20of%20Copy%20command%2C%20but%20you%20can%20automate%20the%20extra%20steps%20to%20copy%20only%20the%20visible%20cells%20through%20Macros.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%20class%3D%22%22%3ETo%20learn%20more%20about%20Macros%2C%20follow%20these%20links%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%20class%3D%22%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FAutomate-tasks-with-the-Macro-Recorder-974ef220-f716-4e01-b015-3ea70e64937b%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3EAutomate%20tasks%20with%20the%20Macro%20Recorder%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%20class%3D%22%22%3E%3CA%20href%3D%22http%3A%2F%2Fwww.excel-easy.com%2Fvba%2Fexamples%2Fmacro-recorder.html%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3EMacro%20Recorder%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739176%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20Default%20to%20Copy%20and%20Paste%20Visible%20Cells%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BI%20have%202%20excel%20files%20(2016).%20One%20selects%20only%20hidden%20cells%20by%20default%2C%20the%20other%20selects%20all%20cells.%20I%20cannot%20figure%20out%20what%20setting%20is%20different%20in%20two%20files.%20Hidden%20cels%20in%20both%20files%20are%20grouped%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1673569%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20Default%20to%20Copy%20and%20Paste%20Visible%20Cells%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1673569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F98730%22%20target%3D%22_blank%22%3E%40Karen%20Arigo%3C%2FA%3EHi%20Karen%2C%20Did%20you%20ever%20find%20the%20answer%20to%20this%3F%20I%20would%20love%20to%20know%20to%20how%20to%20fix%20this%20while%20I%20am%20struggling%20with%20this%20to.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1673772%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20Default%20to%20Copy%20and%20Paste%20Visible%20Cells%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1673772%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20found%20a%26nbsp%3B%20workaround%20that%20someone%20explains%20which%20is%20pretty%20easy%20to%20apply.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fa4accounting.com.au%2Fselect-visible-cells-only-hack%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fa4accounting.com.au%2Fselect-visible-cells-only-hack%2F%3C%2FA%3E%3C%2FP%3E%3CP%3EThe%20basic%20of%20it%20is%20that%20by%20default%20excel%20pastes%20only%20visible%20cells%20in%20case%20of%20a%20filter%20present%20in%20the%20worksheet%20(Anywhere)%20which%20is%20pretty%20logical%20seeing%20that%20filters%20are%20mostly%20applied%20to%20tables.%3C%2FP%3E%3CP%3ESo%20when%20you%20make%20a%20small%20filter%20somewhere%20in%20an%20empty%20space%20of%20the%20worksheet%20than%20it%20will%20apply%20the%20default%20anywhere%20in%20the%20sheet.%3C%2FP%3E%3CP%3Efor%20example%3A%3C%2FP%3E%3CP%3EA300%20Num%3C%2FP%3E%3CP%3EA301%201%3C%2FP%3E%3CP%3EA301%202%3C%2FP%3E%3CP%3EThen%20put%20a%20filter%20on%20Num%20and%20just%20put%20the%20filter%20for%20one%20(right%20click%20A300%20and%20choose%3A%20filter%20%2F%20Filter%20by%20selected%20cell's%20value)%3C%2FP%3E%3CP%3ELeave%20the%20filter%20on%20the%20worksheet%20and%20from%20then%20on%20excel%20by%20default%20will%20only%20paste%20to%20visible%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello,

 

Does anyone know if we can set the Copy and Paste Function to default into copying and pasting only the visible cells? I'm using Excel 2016 in Windows 10.

 

I've been doing the Alt + ; and then copying it way but when copying, I never want the hidden cells anyway. So I'm wondering if this could be set as default to avoid the extra step.

 

Thanks!

3 Replies
Highlighted

Hi,

 

There is no way to change the default action of Copy command, but you can automate the extra steps to copy only the visible cells through Macros.

 

To learn more about Macros, follow these links:

Automate tasks with the Macro Recorder

Macro Recorder

Highlighted

@Haytham Amairah I have 2 excel files (2016). One selects only hidden cells by default, the other selects all cells. I cannot figure out what setting is different in two files. Hidden cels in both files are grouped cells.

Highlighted

I just found a  workaround that someone explains which is pretty easy to apply.

https://a4accounting.com.au/select-visible-cells-only-hack/

The basic of it is that by default excel pastes only visible cells in case of a filter present in the worksheet (Anywhere) which is pretty logical seeing that filters are mostly applied to tables.

So when you make a small filter somewhere in an empty space of the worksheet than it will apply the default anywhere in the sheet.

for example:

A300 Num

A301 1

A301 2

Then put a filter on Num and just put the filter for one (right click A300 and choose: filter / Filter by selected cell's value)

Leave the filter on the worksheet and from then on excel by default will only paste to visible cells.