Home

How to create many (a lot of) tabs that copy the info from the worsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-777515%22%20slang%3D%22en-US%22%3EHow%20to%20create%20many%20(a%20lot%20of)%20tabs%20that%20copy%20the%20info%20from%20the%20worsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777515%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20making%20data%20sheets%20on%20the%20rooms%20of%20a%20building.%20There%20are%20hundreds%20of%20rooms%2C%20and%20i%20need%20individual%20sheets%20for%20each%20one.%20So%20I%20have%20the%20first%20sheet%20made%20(like%20a%20template)%20that%20I%20need%20to%20copy%20over%20many%2C%20many%20times%2C%20to%20then%20begin%20filling%20out.%20Is%20there%20an%20efficient%20way%20to%20do%20this%20(other%20than%20selecting%20one%20tab%2C%20holding%20ctrl%20and%20draggin%20it)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdditionally%2C%20these%20rooms%20are%20numbered%2C%20so%20the%20tab%20will%20have%20the%20same%20number%20as%20the%20tab%20name.%20Is%20it%20is%20possible%20to%20have%20the%20numbers%20name%20themselves%20in%20numerical%20order%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELastly%2C%20Is%20it%20possible%20to%20create%20some%20sort%20of%20index%20page%20that%20links%20to%20each%20of%20these%20tabs%20in%20order%20to%20find%20them%20more%20efficiently.%20Also%20to%20have%20the%20index%20generate%20and%20name%20itself%20based%20on%20the%20title%20of%20each%20tab%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20prefer%20to%20not%20use%20plugins%20if%20it%20is%20at%20all%20possible%3C%2FP%3E%3CP%3EThanks%20for%20the%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-777515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-777575%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20many%20(a%20lot%20of)%20tabs%20that%20copy%20the%20info%20from%20the%20worsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383738%22%20target%3D%22_blank%22%3E%40mbartolucci%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20create%20a%20new%20sheet%20based%20on%20a%20template%20and%20order%20it%2C%20you%20can%20use%20this%20macro%20below%20and%20%3CA%20href%3D%22https%3A%2F%2Fwww.excel-easy.com%2Fvba%2Fexamples%2Fadd-a-macro-to-the-toolbar.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eadd%20it%20to%20the%20Quick%20Access%20Toolbar%3C%2FA%3Eso%20you%20can%20reach%20it%20quickly%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3ESub%20AddSheet()%0A%20%20%20%20Sheets(1).Copy%20After%3A%3DSheets(Sheets.Count)%0A%20%20%20%20ActiveSheet.Name%20%3D%20%22Room%22%20%26amp%3B%20Sheets.Count%20-%202%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20this%20macro%20supposes%20that%20the%20first%20sheet%20is%20the%20template%20and%20the%20second%20is%20the%20Index.%3C%2FP%3E%3CP%3EAlso%2C%20it%20names%20the%20sheets%20like%20the%20following%20order%3A%20%3CSTRONG%3ERoom1%2C%20Room2%2C%20Room3...%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20regard%20to%20the%20Index%20page%2C%20you%20can%20use%20the%20solution%20that%20explained%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.techrepublic.com%2Farticle%2Fuse-index-sheets-to-quickly-navigate-in-an-excel-workbook%2F%23%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3Eto%20do%20so.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAnyway%2C%20I've%20done%20all%20this%20in%20the%20attached%20workbook%20as%20an%20example%20of%20what%20you%20are%20asking%20for.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHope%20that%20helps%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
mbartolucci
Occasional Contributor

I am making data sheets on the rooms of a building. There are hundreds of rooms, and i need individual sheets for each one. So I have the first sheet made (like a template) that I need to copy over many, many times, to then begin filling out. Is there an efficient way to do this (other than selecting one tab, holding ctrl and draggin it)?

 

Additionally, these rooms are numbered, so the tab will have the same number as the tab name. Is it is possible to have the numbers name themselves in numerical order?

 

Lastly, Is it possible to create some sort of index page that links to each of these tabs in order to find them more efficiently. Also to have the index generate and name itself based on the title of each tab?

 

I would prefer to not use plugins if it is at all possible

Thanks for the help! 

1 Reply

@mbartolucci

 

Hi,

 

To create a new sheet based on a template and order it, you can use this macro below and add it to the Quick Access Toolbar so you can reach it quickly:

 

Sub AddSheet()
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Room" & Sheets.Count - 2
End Sub

 

Note: this macro supposes that the first sheet is the template and the second is the Index.

Also, it names the sheets like the following order: Room1, Room2, Room3...

 

With regard to the Index page, you can use the solution that explained in this link to do so.

 

Anyway, I've done all this in the attached workbook as an example of what you are asking for.

 

Hope that helps

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies