Create a tab for each unique cell

Jaffabfc

Board Regular
Joined
Jul 5, 2013
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Morning Everyone,

Tried searching on the forum and online but not finding anything.

I have a lot of data in a tab called "All Items Planner" and in column D i have several thousand cells populated (range changes daily).
In Column D there could be around 20-100 unique lots of data.

Is it possible to create a seperate cell automatically for each unique cell?

Also is it possible to do without a Macro, lots of Macros get removed by the anti-virus where i work and i struggle to keep them active.

Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
have you tried the built-in unique() function?
 
Upvote 0
Hi,

Thanks for the quick response.

Yes i can get that no problem but it shows me around 50 different cells of data (which is correct) but i dont know how to make these into different tabs automatically.
 
Upvote 0
Hi,

Thanks for the quick response.

Yes i can get that no problem but it shows me around 50 different cells of data (which is correct) but i dont know how to make these into different tabs automatically.
apology, missed your new tabs requirement.
It will be easy with a macro, don't really know any other ways expect do it manually.
Sorry
 
Upvote 0
Alright.

Any ideas of the code as i am useless unless i am recording them haha.

I will try and use it on sharepoint and maybe the macros will be allowed on there.
 
Upvote 0
try this, change the sheetname and range to suit

Sub CreateSheetsFromRange()
Dim rng As Range
Dim cell As Range

Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A5")

For Each cell In rng
ThisWorkbook.Sheets.Add after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = cell.Value
Next cell
End Sub
 
Upvote 0
Nice, that works.

A little bit of editting needed and i am sure i will have what i am after.

Thanks
 
Upvote 0
There is a method for creating the sheets without code but it can be a bit long winded:

List the names of the sheets to be created with the heading 'Sheets' in a column.
Create a pivot table from the list you just created.
Drop the 'Sheets' column of the pivot table into the filter section in the field selector.
Go to the 'PivotTable Analyze' section in the ribbon.
Click on the drop down for the 'Options' of the pivot table (Just under the pivot table name)
Select the option 'Show report filter pages'

It will create the sheets with a small pivot table on each sheet.

At that point you would be able to select all of the newly created sheets as an array (multi select the sheets) and then delete the pivot table from them.

You will be left with all of the sheets you need
 
Upvote 0
it did help but i cant answer the question what i thought would be straight forward.

So similar to the above i have a template setup with certain cells having certain formulas in.
Cell A1 is the name of the tab from a formula "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)"

So how do i get all seperate tabs with the list of names (like above) but with a template from another tab (call it template) with all the formulas in.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top