SOLVED

Selection of Multiple Sheets by one click

%3CLINGO-SUB%20id%3D%22lingo-sub-1109635%22%20slang%3D%22en-US%22%3ESelection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1109635%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20select%20multiple%20sheets%20by%20one%20click%20on%20arranged%20shape.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20following%20sheets.%3C%2FP%3E%3CP%3ESheet1%3C%2FP%3E%3CP%3ESheet2%3C%2FP%3E%3CP%3ESheet3%3C%2FP%3E%3CP%3ESheet4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20seelct%20Sheet1%2C%20Sheet2%20and%20Sheet3%20by%20clicking%20on%20a%20shape%2Fobject%2C%20is%20it%20possible%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1109635%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1109731%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1109731%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sulta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20file.%20Click%20the%20image%20and%20all%20sheets%20are%20selected.%20Is%20that%20what%20you%20need%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1109752%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1109752%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20dont%20want%20to%20use%20%3CSTRONG%3EVBA%3C%2FSTRONG%3E%26nbsp%3B!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20please%20note%2C%20i%20need%20to%20select%203%20sheets%20out%20of%204%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1109782%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1109782%22%20slang%3D%22en-US%22%3E%3CP%3Emy%20intention%20is%20to%20report%20to%20management%20with%20some%20specified%20sheets%20to%20show%2C%20from%20100s%20of%20sheets%20in%20the%20work%20book.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20select%20one%20by%20one%20to%20hide%20and%20unhide.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1109797%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1109797%22%20slang%3D%22en-US%22%3EWithout%20VBA%20there%20is%20no%20%221%22%20click%20solution.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20select%20the%20first%20sheet%2C%20hold%20down%20the%20shift%20key%20and%20then%20select%20the%20last%20sheet%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111810%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111810%22%20slang%3D%22en-US%22%3E%3CP%3Eyes%20i%20know%20that%2C%20but%20i%20dont%20want%20to%20select%20all%20sheets%2C%20some%20selective%20sheets%20and%20they%20are%20not%20in%20sequence%20to%20use%20shift%20key.%20In%20case%20of%20vba%2C%20is%20there%20any%20option%20to%20save%20the%20worksheet%20as%20not%20a%20macro%20enabled%20worksheet.%20It%20shall%20circulate%20to%20higher%20management%2C%20and%20we%20cant%20give%20them%20hard%20time%20to%20open%20a%20macro%20enabled%20work%20book%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111922%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111922%22%20slang%3D%22en-US%22%3EYou%20could%20also%20select%20the%20first%20sheet%20you%20want%2C%20then%20the%20last%20sheet%20you%20want%20and%20since%20you%20now%20have%20too%20many%20sheets%20selected%2C%20unselect%20specific%20sheets%20while%20pressing%20down%20the%20CTRL%20key.%3CBR%20%2F%3E%3CBR%20%2F%3EDoes%20that%20work%20for%20you%3F%20If%20not%2C%20VBA%20is%20the%20only%20option.%20But%20the%20good%20news%20are%20that%20yes%2C%20you%20could%20still%20send%20the%20file%20as%20xlsx%20without%20VBA%20code%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111949%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111949%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EYou%20could%20also%20select%20the%20first%20sheet%20you%20want%2C%20then%20the%20last%20sheet%20you%20want%20and%20since%20you%20now%20have%20too%20many%20sheets%20selected%2C%20unselect%20specific%20sheets%20while%20pressing%20down%20the%20CTRL%20key.%3C%2FSTRONG%3E-%20I%20am%20trying%20to%20reduce%20this%20job%2C%20since%20I%20have%20100s%20of%20sheets%20in%20my%20work%20book%20and%20sitting%20each%20time%20to%20do%20this%20is%20impossible.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3EDoes%20that%20work%20for%20you%3F%20If%20not%2C%20VBA%20is%20the%20only%20option.%20But%20the%20good%20news%20are%20that%20yes%2C%20you%20could%20still%20send%20the%20file%20as%20xlsx%20without%20VBA%20code-%3C%2FSPAN%3E%3C%2FSTRONG%3E%26nbsp%3BHow%3F%20during%20closing%20of%20work%20book%20it%20ask%20to%20change%20the%20format%20to%20macro%20enabled%20workbook.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111974%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111974%22%20slang%3D%22en-US%22%3EYou%20can%20integrate%20the%20VBA%20code%20into%20your%20personal%20macro%20workbook%20which%20means%20that%20macro%20is%20always%20available%20when%20you%20open%20excel.%3CBR%20%2F%3EYou%20can%20then%20add%20a%20button%20to%20run%20the%20macro%20to%20a%20custom%20ribbon%20in%20your%20excel%20menu%20so%20it's%20always%20there%20when%20you%20need%20it.%3CBR%20%2F%3EThen%20you%20open%20the%20file%20with%20the%20100%20sheets%2C%20click%20on%20the%20button%20in%20the%20ribbon%20and%20voil%C3%A0%2C%20the%20sheets%20that%20you%20need%20are%20selected.%3CBR%20%2F%3E%3CBR%20%2F%3ELet%20me%20know%20with%20which%20of%20these%20steps%20you%20need%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1112300%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1112300%22%20slang%3D%22en-US%22%3E%3CP%3Ecould%20you%20explain%20step%20by%20step%2C%20how%20to%20do%20that%20and%20please%20give%20me%20an%20example%20file%20too%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1114434%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1114434%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sulta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20attached%20video%20explains%20how%20to%20do%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1116820%22%20slang%3D%22en-US%22%3ERe%3A%20Selection%20of%20Multiple%20Sheets%20by%20one%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1116820%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcellent%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20a%20lot.%20it%20works%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi

 

How to select multiple sheets by one click on arranged shape.

 

Eg

 

I have following sheets.

Sheet1

Sheet2

Sheet3

Sheet4

 

I need to seelct Sheet1, Sheet2 and Sheet3 by clicking on a shape/object, is it possible

 

 

11 Replies
Highlighted

Hi @Sameer_Kuppanath_Sulta 

 

Please see attached file. Click the image and all sheets are selected. Is that what you need?

Highlighted

Hi 

 

@PascalKTeam 

 

I dont want to use VBA !

 

Also, please note, i need to select 3 sheets out of 4 sheets. 

 

 

 

 

 

 

 

 

 

Highlighted

my intention is to report to management with some specified sheets to show, from 100s of sheets in the work book. 

 

I cannot select one by one to hide and unhide. 

Highlighted
Without VBA there is no "1" click solution.

You could select the first sheet, hold down the shift key and then select the last sheet
Highlighted

yes i know that, but i dont want to select all sheets, some selective sheets and they are not in sequence to use shift key. In case of vba, is there any option to save the worksheet as not a macro enabled worksheet. It shall circulate to higher management, and we cant give them hard time to open a macro enabled work book@PascalKTeam 

Highlighted
You could also select the first sheet you want, then the last sheet you want and since you now have too many sheets selected, unselect specific sheets while pressing down the CTRL key.

Does that work for you? If not, VBA is the only option. But the good news are that yes, you could still send the file as xlsx without VBA code
Highlighted

You could also select the first sheet you want, then the last sheet you want and since you now have too many sheets selected, unselect specific sheets while pressing down the CTRL key.- I am trying to reduce this job, since I have 100s of sheets in my work book and sitting each time to do this is impossible. @PascalKTeam 

 

Does that work for you? If not, VBA is the only option. But the good news are that yes, you could still send the file as xlsx without VBA code- How? during closing of work book it ask to change the format to macro enabled workbook. 

Highlighted
You can integrate the VBA code into your personal macro workbook which means that macro is always available when you open excel.
You can then add a button to run the macro to a custom ribbon in your excel menu so it's always there when you need it.
Then you open the file with the 100 sheets, click on the button in the ribbon and voilà, the sheets that you need are selected.

Let me know with which of these steps you need help
Highlighted

could you explain step by step, how to do that and please give me an example file too@PascalKTeam 

Best Response confirmed by Sameer_Kuppanath_Sulta (Contributor)
Solution

Hi @Sameer_Kuppanath_Sulta 

 

I hope attached video explains how to do it

Highlighted

 

Excellent

 

thanks a lot. it works

@PascalKTeam