Compile Data from 16 sheets into one list on seperate sheet.

[JRT2006]

Okay, quick background to give you an idea of what I've attempting to do and why.
15 friends and I run an online football league, myself being the commisioner.
I've created a pretty basic workbook with 17 different sheets, Sheets 1-16 are dedicated to each position on the football field (QB, RB, WR, TE, etc.) These sheets will be used for scouting prospects for drafts, you type the players name and enter a letter grade A thru F under an a skill and a formula gives a point grade for the player.

Sheet 17 is labeled 'Big Board'. On this sheet, I am trying to get some of the information from each sheet (player name, position, projected round, and draft grade) compiled into a list and sorted by two different criteria, first would be the projected draft round and second would be the players grade.

This "big board" is what myself and friends will base our drafting of future prospects off of, the big board will sort the best players to take by round and final grade. I just dont know how to do it.
Vlookup over multiple sheets doesnt work, or i cant get it to. I also tried some code copied to try and do a VLOOKUPAllSheets and that returns a Name? error.

Any ideas?

 

[Trebor76]

Consolidating non constsient data can be problematic. That said, try this (note I've also put in extra code to automatically clear any data in the wstConsTab):

Option Explicit
Sub Macro5()

    Const lngStartRow As Long = 2 'Starting row number for the data in the football position tabs. Change to suit.

    Dim lngEndRow As Long, _
        lngPasteRow As Long, _
        lngMyRow As Long
    Dim wstMyTab As Worksheet, _
        wstConsTab As Worksheet
    Dim rngFinalGrade As Range
    Dim strFinalGradeCol As String
        
    Application.ScreenUpdating = False
    
    Set wstConsTab = ThisWorkbook.Sheets("Big_Board") ' Consolidation worksheet name. Change to suit.
    
    'Clears the existing data in the 'wstConsTab' to avoid duplicate results
    On Error Resume Next
        lngEndRow = wstConsTab.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Assumes the required to be put into the consolidated tab is across Col's A to D. Change if required.
        If lngEndRow >= lngStartRow Then
            wstConsTab.Range("A" & lngStartRow & ":D" & lngEndRow).ClearContents
        End If
    On Error GoTo 0
    
    For Each wstMyTab In ThisWorkbook.Sheets
        'Only try and consolidate tab data where:
        '1. The tab is visible
        '2. The tab name is not the same as the defined name for the consolidation tab
        '3. The tab name is not called 'Team_Needs'
        If wstMyTab.Visible = xlSheetVisible And wstMyTab.Name <> wstConsTab.Name And wstMyTab.Name <> "Team_Needs" Then
            On Error Resume Next 'Account for there being no data on the tab
                lngEndRow = wstMyTab.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If lngEndRow >= lngStartRow Then
                    For lngMyRow = lngStartRow To lngEndRow
                        If Len(wstMyTab.Range("A" & lngMyRow)) > 0 Then
                            lngPasteRow = wstConsTab.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Assumes the required to be put into the consolidated tab is across Col's A to D. Change if required.
                            If lngPasteRow = 0 Then
                                lngPasteRow = lngStartRow
                            Else
                                lngPasteRow = lngPasteRow + 1
                            End If
                            wstConsTab.Range("A" & lngPasteRow).Value = wstMyTab.Range("A" & lngMyRow).Value
                            wstConsTab.Range("B" & lngPasteRow).Value = wstMyTab.Range("E1").Value
                            wstConsTab.Range("C" & lngPasteRow).Value = wstMyTab.Range("D" & lngMyRow).Value
                            'Need to find the column where the text 'Final Grade' is in Row 1
                            With wstMyTab.Rows("1")
                                Set rngFinalGrade = .Find(What:="Final Grade", After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                                If Not rngFinalGrade Is Nothing Then
                                    strFinalGradeCol = Left(rngFinalGrade.Address(True, False), Application.WorksheetFunction.Search("$", rngFinalGrade.Address(True, False)) - 1)
                                End If
                                If strFinalGradeCol <> "" Then
                                    If IsError(wstMyTab.Range(strFinalGradeCol & lngMyRow).Value) = True Then
                                        wstConsTab.Range("D" & lngPasteRow).Value = 0
                                    Else
                                        wstConsTab.Range("D" & lngPasteRow).Value = wstMyTab.Range(strFinalGradeCol & lngMyRow).Value
                                    End If
                                End If
                            End With
                        Else
                            Exit For
                        End If
                    Next lngMyRow
                End If
            On Error GoTo 0
        End If
    Next wstMyTab
    
    Application.CutCopyMode = False
    
    lngEndRow = wstConsTab.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    ActiveWorkbook.Worksheets(CStr(wstConsTab.Name)).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(CStr(wstConsTab.Name)).Sort.SortFields.Add Key:=Range("D" & lngStartRow & ":D" & lngEndRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets(CStr(wstConsTab.Name)).Sort
        .SetRange Range("A" & lngStartRow & ":D" & lngEndRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Set wstConsTab = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Data has now been consolidated.", vbInformation

End Sub

Robert

 

[JRT2006]

This works perfect. Just as it did before but better because the information erases when you run the macro again =)
Any idea what i did to screw it up before? because it worked fine.......did i shift data to the wrong column throwing everything out of whack?

Also, will it hurt anything to create a few buttons and attach the macro to them so users can just press a button rather than alt+F8?

 

[Trebor76]

Any idea what i did to screw it up before?

It was mainly due to the two points I noted above.

Also, will it hurt anything to create a few buttons and attach the macro to them so users can just press a button rather than alt+F8?

No, that will be fine. You can also remove the message about clearing the data across columns K to N in the wstConsTab sheet as the macro does it for you now.

Regards,

Robert

 

[JRT2006]

Thanks for your help. hopefully, HOPEFULLY, i wont be back here for this same project.

 

[JRT2006]

Robert, I'm back. Not for help as the code has been working flawless.
Looking for an opinion and figured it would be a wasted thread start if it was an easy one post answer.

Not sure if there is a formula for this or if it requires code. I was thinking of building a little box next to the big board that searches the prospects and recommends a selection based on certain criteria.

The box would be a simple table with:

Player Name
Player Position
Player Value

and the function/code would scan the big board results and auto fill the above table.
I would want it to find best value first, once it found best value (example highest value is 286.11), it would locate position (QB), after that it would look for all positions with QB and look at Position rank (2). Since position rank wasn't the highest(1) it would move on to the next highest 'best value' and repeat the steps until it found position rank '1'.

Is that even possible or am I thinking way out of excels reach?

 

[Trebor76]

Hi there,

My suggestion would be to put a filter across the heading row of columns A to D to simply get (filter) the results you're after. Just make sure the data isn't filtered when you consolidate.

HTH

Robert

 

[ndnoutlaw]

In this code
Application.WorksheetFunction.Search("$"

what does the "$" mean?

 

[Trebor76]

In this code
Application.WorksheetFunction.Search("$"

what does the "$" mean?

It is simply looking for the dollar sign i.e. $ within the 'rngFinalGrade' range address.

HTH

Robert

 

[ndnoutlaw]

It is simply looking for the dollar sign i.e. $ within the 'rngFinalGrade' range address.

HTH

Robert

That's what I thought it was but was just making sure. Thank you Robert

P.S. I'm working on a file that you help someone with. The football excel file, except I decided to build my own. I've been going through your code, a lot of this is still Greek to me but I'm making progress