create new tab taking data from one column

I've created a PivotTable version that may work for you. See my comments on how to use it farther down:

https://1drv.ms/x/s!Am8lVyUzjKfpoCWxBLSbfkUro9sL?e=SJFD7Q

What is the "No." in the first column?  What is the point of the line numbers in column A?

Why do you have a whole bunch "9.0 Year End to June 30 2018" headings?  Same with 10.0's ...?

Your "All Data" tab is setup more like a final report.  If you make the input table just that, input only, then you can add validations and rules. Then worry about splitting out the year end reports and totals.

The "total" column should be just that, a calculated total, summing the other columns.

Jeovany's example points out one problem with your current input sheet, that has been already mentioned. The problem of no input validation, so you have both "40 Cent" and "Fourty Cent" as variations on the same company name.  His macro has created 2 separate tabs for the 2 names, not what you want.  I've confirmed that in my version of the workbook the second "Farell & Clark" entry has a trailing space, bad data entry which is corrected using excel validation

add drop-down lists in Excel     2014 04 22

https://www.ablebits.com/office-addins-blog/2014/04/22/excel-add-drop-down-list/

Adding a drop-down list to your table is a preliminary that can save a lot of your time when working in Excel. If you want to make sure you use a limited set of values in some column, Excel data validation is exactly what you need. Watch this video to see how easy it is.

use one of these 2 options:

• Drop down box based on a named range
• Excel data validation list from table

**********

There is no way to automate a slicer for fiscal year, so you can brute force it:

**Slicers for Fiscal Years** .  201? 05 06   Mynda Treacy ****http://www.myonlinetraininghub.com/excel-slicers-for-fiscal-years

There’s no built-in way to create Excel Slicers for fiscal years, however the solution is easily achieved by adding an extra column to your source data to classify each date into its relevant fiscal year.

Actually, you could probably create a formula to round the entry date to the fiscal year end ...

************

Hint, example data should be much simpler:

• 2 or 3 companies
• just a few data rows for each company
• simple numbers to make it easy to do addition in your head

I've created an example with following features:

• Input data is now an Excel Table
• Company is now a drop down input, from a filtered list
• Totals column moved to end, calculated as sum, not data entry
• I've added a new "fiscal year end" column
• I've added Slicers for fiscal year and company name
• I've cut the input data down to just 2 fiscal years to play with
• I've sorted the input table, but you can resort it yourself on any column(s) you need
• I've highlighted the totals column as a clue that it is not data entry.  Actually, you should consider removing it from data entry, it can be added back in pivot.

From the input data I've created a Pivot Table with following features:

• Slicers for Fiscal Year and Company
• after making "All Data" tab changes, ie new rows, changed data in an existing row, just right click on Pivot table and select "Refresh" to import the new data and recalculate

When doing data entry, you can use slicer to limit display to fiscal year and company. Just enter the data in the row at the bottom of the displayed.

Here is the overal pivot table:

Here is 2 year pivot summary for 1 company

Note the company name that is showing duplicate entries. Probably due to bad entry of company name, with extra spaces. 

Here is results for first Brehany entry:

Here is result for second entry:

Here is report after I copied a single entry of the "Breheny" text into all of the columns

this new single entry reflects the correct total.  I haven't checked Jeovany's example, but I bet it has 2 separate tabs.

***************

If you are going to insist on separate tabs here are a couple of tips you may want to consider

You will have too many tabs to find them easily/quickly, here are 2 ideas for faster navigation: a drop down, and a generated "index" "Table of Contents" page hyperlinked to the tabs

**Show Specific Sheets in Excel******June 16, 2016 (dropdown to access tabs)

****In a workbook with lots of worksheets, you can have a hard time finding the sheets that you need, to do a specific task. Instead of scrolling through all the sheet tabs, or using…

“Too Many Worksheets” Driving You Nuts? Read This!              Feb 16, 2009****https://chandoo.org/wp/2009/02/16/excel-table-of-contents-etc/****

Learn how to create Table of Contents in Excel and other tricks to manage when you have too many worksheets.

Add a macro that displays the Excel Worksheet Navigation list

https://www.techrepublic.com/blog/microsoft-office/add-a-macro-that-displays-the-excel-worksheet-navigation-list/ 

Offer users a bit of sheet-jumping flexibility. Use an Excel macro to display the Sheet Navigation list, anytime, anywhere.

Add a Table of Contents Image Gallery Sheet to Your Excel Files                   October 5, 2016      Jon Acampora

https://www.excelcampus.com/vba/table-of-contents-gallery/

****Make your hard work shine, and learn how to add a beautiful Table of Contents sheet to the front of your Excel workbook that contains clickable image tiles to each sheet in the workbook.

Often times our workbooks contain a lot of sheets, and users have a difficult time finding the chart, dashboard, report, or data sheet they are looking for.  They don’t know what is included in the workbook until they scroll through all the sheet tabs.

The Table of Contents Gallery solves this by adding a nice looking navigation sheet to the front of your workbook.  This TOC Gallery sheet contains thumbnail images of each sheet in the workbook.  Each image tile is a clickable hyperlink that jumps to the sheet.  Table Of Contents Gallery.xlsm

Make Excel tabs list in a worksheet8 March 2019

https://office-watch.com/2019/make-excel-tabs-list-in-a-worksheet/

Make your own clickable list of workbook tabs in an Excel worksheet to workaround the small number of tabs that can fit in a single line.

Automatic worksheet/tabs list in Excel 10 March 2019

https://office-watch.com/2019/automatic-worksheet-tabs-list-in-excel/

An automatically updated list of all worksheets or tabs in Excel but there are ‘gotchas’ which aren’t often mentioned. Making a list of worksheets is a thing you might expect to be easy but is almost ludicrously intricate.  There’s no direct function to do it and the current method relies on a very old and officially obsolete Excel function (which has no modern equivalent for reasons passing understanding). The steps are straightforward, even if you don’t understand the functions and formulas involved. We’ll break it down so you can understand how the whole thing comes together. Make automatic Excel worksheet list or table of contents - 18 March 2019

https://office-watch.com/2019/make-automatic-excel-worksheet-list-or-table-of-contents/

Continuing our look at Excel tab/worksheet listing here’s a fully automated alternative with more control and filtering. There are many VBA code samples on the Internet, we’ve taken one we like and added some extras based on questions from readers. Each tab has a clickable link, the worksheet type and visibility.

*******************************

Here are some more tab related ideas for you

Alphabetizing Worksheet Tabshttp://excelribbon.tips.net/T013440_Alphabetizing_Worksheet_Tabs.html****As you get more and more worksheets into a workbook, you'll find yourself moving them around into different sequences. You may even want to put the worksheets into alphabetic order. This tip provides two macros that can handle sorting for you.

Hide, unhide, delete and sort worksheets all from one userformhttp://www.myonlinetraininghub.com/hide-delete-and-sort-sheets-with-vba-userform****You have a workbook with lots of sheets and you want to do things like hide sheets, delete some sheets or just sort them.

By using a very simple userform and some VBA we can do these things with a few clicks of our mouse.

**Ordering Worksheets Based on a Cell Value**

https://excelribbon.tips.net/T012448_Ordering_Worksheets_Based_on_a_Cell_Value

Need to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a macro you can make this ordering change relatively easily.****

Change sheet tab color based on cell value

https://www.extendoffice.com/documents/excel/4090-excel-color-tab-based-on-cell-value.html

Normally, you can change the tab color easily and quickly in Excel, but, have you ever tried to change the tab color based on cell value in a worksheet? If you are intrested in this task, I will talk about some codes for you to color the sheet tab based on a specific cell value in Excel.

**Create List of Names In a Workbook******          September 25, 2009

https://contexturesblog.com/archives/2009/09/25/create-list-of-names-in-a-workbook/

Well, I finally finished creating all the names in my client’s Excel workbook, and sent the file back to them. To help my client see what I’d done, I created a list of the...

**Creating Individual Workbooks** ****http://excelribbon.tips.net/T012273\_Creating\_Individual\_Workbooks.html

Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based on each worksheet, here’s the information you need.

Dynamically Changing Worksheet Tab Color          (last updated August 6, 2018) Allen Wyatt

https://excelribbon.tips.net/T013138_Dynamically_Changing_Worksheet_Tab_Color.html

Andrew knows how to change the color of worksheet tabs manually. However, he would like a way to change the tab color based upon a value in a cell on the worksheet.

https://1drv.ms/x/s!Aq3WqOz73fYygYUUlnjJxj5jNBE0ow?e=sAkOJJ

Hi

see link attached. I thought I had done the question answered, I will do that.

" Is the master sheet the one that needs the overhauling "

You need to understand that 

Your inputting system is not the correct for the tasks your boss is demanding from you, And it creates problems rather than solutions 

I can help you if and only if you send me a proper file I can work with 

"Not like the others with incomplete data"

The new inputting system will have some similarities to the one you have 

But will be more easier and reliable 

Now

May I politely ask you,

Why you haven't mark the question you posted, that I hardly worked on to solve your problem, as answered, when you had stated that "it works well and it helped you"?

You didn't even say "Thank you"

Send the file to move further with your problem

Hi Again

The master sheet needs to stay as it is, for the inputter to just enter the data, so any ideas you think will achieve the end goal, I am more than willing to listen to you.

https://1drv.ms/x/s!Aq3WqOz73fYygYUUlnjJxj5jNBE0ow?e=EVu3y0

Copy of latest file

Regards

Stephen