How to Create Tabs Within Tabs in Excel
HTML-код
- Опубликовано: 26 июн 2024
- In this video, I'll guide you through multiple steps to create tabs within tabs in Excel. You'll learn about organizing required datasets, creating multiple tabs, aligning the tabs, creating a VBA code, assigning a VBA code with tabs, and compiling all tabs. Creating tabs within tabs can be used to organize and enhance the presentation of data, enabling effective sorting and display within a limited space. With practical examples and step-by-step instructions, you can effortlessly make tabs within tabs in your Excel spreadsheets.
👨🏫 Instructor: Farihatul Mim
🎥 Editor: Mohammad Ullah
✨ ⯆ Resources:
Alt+F11 - To open the Visual Basic
▬ Contents of this video ▬▬▬▬▬▬▬▬▬▬
0:00 - Intro
0:40 - Organizing required Datasets
1:15 - Creating multiple tabs
3:35 - Aligning the tabs
5:24 - Creating a VBA Code
7:50 - Assigning VBA Code with tabs
8:49 - Compiling all tabs
📚 ⯆ DOWNLOAD the workbook here:
www.exceldemy.com/create-tabs...
🌍 ⯆ Checkout the article here:
www.exceldemy.com/create-tabs...
🚩 Stay connected with us on social media for more Excel tips and tricks!
Facebook: / exceldemy
Twitter: / exceldemy
LinkedIn: / exceldemy
🙋♂️ Stuck with an Excel formula or a VBA code? You can post your questions or upload your Excel file to get in touch with the professionals and get the solution you need.
ExcelDemy Forum: exceldemy.com/forum/
👉 If you found this video helpful, don't forget to subscribe to our channel for more Excel tutorials, tips, and tricks! Hit the subscribe button and turn on notifications, so you never miss an upload. By subscribing, you'll be supporting our channel and helping us to reach more people who can benefit from our content. Thank you for watching, and we'll see you in the next video!
🔔 ⯆ Subscribe on RUclips:
/ @exceldemy2006
#excel #exceltutorial #exceltips #exceltricks
That was very creative! Thank you for sharing this video. I would’ve put this into a pivot chart and used the three tables as a filter at the top so I could just chose what table I wanted to see. There are other ways to set this up too but this way allows for a lot of options!
creative. well explained. i am thankful. please keep uploading such creative excel reporting/presentation videos.
Thanks for the good work. Keep uploading creative content. I am very happy to see useful content.😀😀😀🥳🥳🎉🎉
Thank you for a Very well-done and pleasant to hear/follow video. Appreciate the steps, I wish you the best of luck.
Creative but I think that using Power Query and slicers would be much easier. This solution also requires no code at all.
Hello, can i please explain how to do it?
Awesome 👏
good job, nice one.
The way you are explaining/teaching is very nice. there are little bit difference between vba code showing in video and in the attached file. However its very nice vide.
Well done & thank you.
Hello @anonymoususer4356,
Thanks for your appreciation. You are most welcome. Please stay connected with us.
Regards
ExcelDemy
Excelente su creatividad y lo fácil que lo haces ver, de verdad impresionado. Gracias. Me agradaría hacer algo así con mi trabajo...
Dear, Thanks for your nice words! Your appreciation means a lot to us.
Cool 🎉
Wow
Hi, thank you so much for sharing this amazing concept. Pardon me, but when I attempted to add checkboxes to the sheet, they kept appearing in each tab. Is there a way to have each checkbox appear in a tab separately? Thanks in advance.
Dear, Thanks for thanking us! You are very welcome. We are glad that you have found the concept excellent. You tried adding checkboxes instead of typical shapes. However, you found it challenging to implement the idea using checkboxes.
Don't worry! There is a way to have each checkbox appear separately in the intended tab. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Create-Tabs-Within-Tabs-by-adding-checkboxes-instead-of-typical-shapes.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Ayman-Farag-SOLVED.xlsm
Excel VBA Code:
Sub TabEpl()
With Sheet1
.Range("B:H").EntireColumn.Hidden = False
.Range("I:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabBundesliga()
With Sheet1
.Range("I:O").EntireColumn.Hidden = False
.Range("B:H,P:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabSeieA()
With Sheet1
.Range("P:V").EntireColumn.Hidden = False
.Range("B:O").EntireColumn.Hidden = True
End With
End Sub
Private Sub CheckBox1_Change()
If Sheet1.CheckBox1.Value = True Then
Sheet1.CheckBox2.Value = False
Sheet1.CheckBox3.Value = False
Sheet1.CheckBox2.Visible = False
Sheet1.CheckBox3.Visible = False
Sheet1.CheckBox1.Left = Range("C4").Left
Sheet1.CheckBox1.Top = Range("C4").Top
Call TabEpl
Else
Sheet1.CheckBox2.Visible = True
Sheet1.CheckBox3.Visible = True
Sheet1.CheckBox1.Left = Range("B4").Left
Sheet1.CheckBox1.Top = Range("B4").Top
Sheet1.CheckBox2.Left = Range("D4").Left
Sheet1.CheckBox2.Top = Range("D4").Top
Sheet1.CheckBox3.Left = Range("F4").Left
Sheet1.CheckBox3.Top = Range("F4").Top
End If
End Sub
Private Sub CheckBox2_Change()
If Sheet1.CheckBox2.Value = True Then
Sheet1.CheckBox1.Value = False
Sheet1.CheckBox3.Value = False
Sheet1.CheckBox1.Visible = False
Sheet1.CheckBox3.Visible = False
Sheet1.CheckBox2.Left = Range("J4").Left
Sheet1.CheckBox2.Top = Range("J4").Top
Call TabBundesliga
Else
Sheet1.CheckBox1.Visible = True
Sheet1.CheckBox3.Visible = True
Sheet1.CheckBox1.Left = Range("I4").Left
Sheet1.CheckBox1.Top = Range("I4").Top
Sheet1.CheckBox2.Left = Range("K4").Left
Sheet1.CheckBox2.Top = Range("K4").Top
Sheet1.CheckBox3.Left = Range("M4").Left
Sheet1.CheckBox3.Top = Range("M4").Top
End If
End Sub
Private Sub CheckBox3_Change()
If Sheet1.CheckBox3.Value = True Then
Sheet1.CheckBox1.Value = False
Sheet1.CheckBox2.Value = False
Sheet1.CheckBox1.Visible = False
Sheet1.CheckBox2.Visible = False
Sheet1.CheckBox3.Left = Range("R4").Left
Sheet1.CheckBox3.Top = Range("R4").Top
Call TabSeieA
Else
Sheet1.CheckBox1.Visible = True
Sheet1.CheckBox2.Visible = True
Sheet1.CheckBox1.Left = Range("P4").Left
Sheet1.CheckBox1.Top = Range("P4").Top
Sheet1.CheckBox2.Left = Range("R4").Left
Sheet1.CheckBox2.Top = Range("R4").Top
Sheet1.CheckBox3.Left = Range("T4").Left
Sheet1.CheckBox3.Top = Range("T4").Top
End If
End Sub
This tutorial is awesome. If I wanted to add a second tab in excel, how would the coding change?
Dear @kevin66rn,
Thank you for reaching out, and we appreciate your feedback. You asked an interesting question and the Exceldemy Forum is here to provide the answers to your questions free of cost.
All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need.
Exceldemy Forum: exceldemy.com/forum/
Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems.
Make sure to stay connected with Exceldemy!❤🥳.
Regards,
Exceldemy
Hi there, great video and thanks for sharing. Is there any way to duplicate the tab in the same workbook and have the new tab contain all of the same macros? Thanks in advance!
Dear, Thanks for your kind words. Your appreciation means a lot to us. You want to duplicate the tabs in the same workbook but use the same macros. To do this, you have to use the ActiveSheet keyword instead of a particular sheet name. Also, you have to keep all the sub-procedures in a module.
Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/Duplicating-the-tabs-in-the-same-workbook-and-using-the-same-macros.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/Rory-Bryant-SOLVED.xlsm
Improved Excel VBA Sub-procedures:
Sub TabEpl()
On Error Resume Next
With ActiveSheet
.Shapes("EplOn").Visible = msoCTrue
.Shapes("EplOff").Visible = msoFalse
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("B:H").EntireColumn.Hidden = False
.Range("I:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabBundesliga()
On Error Resume Next
With ActiveSheet
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoCTrue
.Shapes("BundOff").Visible = msoFalse
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("I:O").EntireColumn.Hidden = False
.Range("B:H,P:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabSeieA()
On Error Resume Next
With ActiveSheet
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoCTrue
.Shapes("SerieOff").Visible = msoFalse
.Range("P:V").EntireColumn.Hidden = False
.Range("B:O").EntireColumn.Hidden = True
End With
End Sub
how can i do it for google excel..pls can you make video on it !!
Dear, you need some AppleScript functions to develop such tabs within a tab in Google Sheets. The rest of the procedures will be almost like those in Excel.
Of course! We will try as requested.
@@exceldemy2006 Thanks a lot !!
Dear,
You are most welcome. Please stay connected with us.
Regards
ExcelDemy
I would like to create 7 tabs. How much more complicated would this be
Dear, thanks for the comment. If you want to create 7 tabs, there is nothing to worry about. You just need to make several more button shapes and sub-procedures. The rest of the ideas will be the same.
You can share your problem within the ExcelDemy Forum if you have found difficulties when creating these tabs.
ExcelDemy Forum: exceldemy.com/forum/