How can I extract data from one tab on a specific individuals to numerous tabs in Excel?

We have a sales admin that downloads data from our system daily and then what she was doing was filtering by Engineer, then cutting and pasting in each individual tab the information associated with each specific ENG (takes her several hours to complete the entire process DAILY). I've linked the fields from the TOTAL tabs to all of the individual tabs with Engineer names. Is there a formula that can take the information associated with the specific assigned ENG (column K in TOTAL tab) and filter it out to the other tabs? They don't want them filtering from the main TOTAL tab. And the ENG said it's easier if they can just go their tab and look at their projects.  I have a couple of other things I would like to do to the spreadsheet to make her job easier but I'm starting with this first. Once I complete this step, I will ask the other questions.
Marcia MorrisProject ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Ejgil HedegaardConnect With a Mentor Commented:
Here is a solution using only 2 calculations (array formulas) on each sheet, to set the start and end row for the engineer.
The values are then retrieved with index formulas, and index is so so fast you will not notice the calculation.
File size is only 744 kB.

The engineer "name" is in A3, and if none I put an x, because not assigned is empty on Total.
First row in A4, and last row in A5.
The index start at row 13, so 1 means the start row for the index range, not the row on the sheet.
It is required that Total sheet is sorted by Engineer first, to ensure that it is possible to set a start and end row for each engineer.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Marcia,

I suggest you to go for a VBA solution instead of formulas because as the data grows and with Array formulas on all the sheets, the file size will increase unnecessarily and slow down the sheets calculation.

If you are open to a VBA solution, please try this....

The problem right now is that in Col. K on Total sheet, you have abbreviated names of Engineers while you have named all the engineers sheets with their full name. There is no way for either a formula of a code to know their full name.
It's better to have a lookup table on a sheet so that the code can find their full names.
The code given below will create the sheets for all the engineers listed in col. A and transfer their own data to their own sheets.
You may click the button called "Copy Data To Individual Sheets" on the Total Sheet to run the code.

If you have difficulty in downloading and opening the attached file due to a temporary bug in the forum, first download and save it on your system and then open it.

Since this is a macro enabled workbook, you will need to enable the macros when prompted to run the code.

Sub TransferDataToRelevantSheets()
Dim sws As Worksheet, dws As Worksheet, Temp As Worksheet
Dim slr As Long, dlr As Long, tlr As Long
Dim sRng As Range, sCell As Range, tRng As Range, tCell As Range
Dim Engineer As String

Application.ScreenUpdating = False

Set sws = Sheets("Total")
slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
If slr < 13 Then
    MsgBox "There is no data on Total Tab.", vbCritical, "Data Not Found!"
    Exit Sub
End If
Sheets.Add(before:=Sheets(1)).Name = "Temp"
Set Temp = ActiveSheet
sws.Range("K13:K" & slr).Copy Temp.Range("A1")
Temp.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
tlr = Temp.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set tRng = Temp.Range("A1:A" & tlr)
For Each tCell In tRng
    With sws.Rows(12)
        .AutoFilter field:=11, Criteria1:=tCell
        If sws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            On Error Resume Next
            Set dws = Sheets(tCell.Value)
            dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row
            If dlr > 11 Then dws.Rows("12:" & dlr).Clear
            On Error GoTo 0
            If dws Is Nothing Then
                Sheets.Add after:=Sheets(Sheets.Count)
                If tCell = "" Then tCell.Value = "UnAssigned"
                ActiveSheet.Name = tCell.Value
                Set dws = ActiveSheet
            End If
            sws.Range("A12").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy dws.Range("A12")
        End If
    End With
    Set dws = Nothing
Next tCell
sws.AutoFilterMode = 0
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

Marcia MorrisProject ManagerAuthor Commented:
Ejgil.....thank you so much!!!! It worked like a charm. I am going to do a run through with the Sales Admin tomorrow since she was out Friday. Then I will proceed with asking part 2 of my question which has to do with one of the other tab she had in the spreadsheet that I wasn't clear on.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Rob HensonFinance AnalystCommented:
Is the data in a format that can be summarised in a Pivot Table?

If so, you can create a pivot table with Engineer as the Page/Report filter and there is a setting whereby it will create a tab for each Page.

Rob H
Marcia MorrisProject ManagerAuthor Commented:
Ejgil..The Sales Admin reviewed the spreadsheet and asked for some additional items that she was also doing manually.
  1. 1. I was trying to include a conditional format to make the entire row of of each tab highlight yellow if it contains the text "large package". I don't know what I am not doing correct in the 'total' tab, but it only highlighted the cell with the words large package.
  2. 2. Is it possible to identify all line items in the "Total" tab with a Bid Due Date (column I) of {3 weeks ago from today → yesterday’s date} Example: Today is 8/11, so the late range would be 7/20 – 8/10, and extract these to the "Late Tab"? They already show up in each Estimator tab but they want to see what's late in one tab. Basically every day they look at what's late from the system they export the data out of.
Ejgil HedegaardCommented:
1. Columns A:N highlighted for rows containing "Large Package" in column E.

2. Formula in column O on Total identify the late rows, setting an increasing number to use on the Late tab.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.