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]

OK try this (if this is still wrong and you cannot fix it you'll have to try and upload the file):

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
        
    Application.ScreenUpdating = False
    
    Set wstConsTab = ThisWorkbook.Sheets("Consolidate") ' Consolidation worksheet name. Change to suit.
    
    For Each wstMyTab In ThisWorkbook.Sheets
        If wstMyTab.Name <> wstConsTab.Name 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
                    lngPasteRow = wstConsTab.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 '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
                    End If
                    '//This is where the data from the individual sheets are being consolidated to the 'wstConsTab' sheet. Change as required.//
                    wstMyTab.Range("A" & lngStartRow & ":A" & lngEndRow).Copy
                        wstConsTab.Range("A" & lngPasteRow).PasteSpecial xlPasteValues
                    wstConsTab.Range("B" & lngPasteRow & ":B" & lngPasteRow + lngEndRow - lngStartRow).Value = _
                        wstMyTab.Range("E1").Value
                    wstMyTab.Range("D" & lngStartRow & ":D" & lngEndRow).Copy
                        wstConsTab.Range("C" & lngPasteRow).PasteSpecial xlPasteValues
                    wstMyTab.Range("BA" & lngStartRow & ":BA" & lngEndRow).Copy
                        wstConsTab.Range("D" & lngPasteRow).PasteSpecial xlPasteValues
                End If
            On Error GoTo 0
        End If
    Next wstMyTab
    
    Application.CutCopyMode = False
    
    lngEndRow = wstConsTab.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = lngEndRow To lngStartRow
        wstConsTab.Rows(lngMyRow).SpecialCells(xlBlanks).EntireRow.Delete
    Next lngMyRow
    
    Set wstConsTab = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Data has now been consolidated.", vbInformation

End Sub

Robert

 

[JRT2006]

Robert, sir, you were amazing help.

This will work fine, it gathers the information needed, the guys and I can remove the blank cells ourselves pretty easily.
I thank you for your time.
I'll never understand code - too much for me to remember.

 

[Trebor76]

You're welcome.

The last For loop should delete all blank rows though

Thanks for the like

Cheers,

Robert

 

[JRT2006]

I'm wondering if because each row in column B is populated with position, regardless if there is any data in column A, its not considering the entire row blank, therefor not removing it. For example, the sample board below is the type of result given.

John Doe	RB	1	255.56
Jack Deer	RB	4	250.00
	RB
	RB
	RB
	RB
	RB
	RB
	RB
	RB

<tbody>
</tbody>

Im not sure how hard it is to add into the code, but I'm trying to get the results to sort Column D from largest to smallest.
I can simply just use the sort option of doing so, but if you are going to continue to work your brain on this, could that be added in there?
If not, I'm completely fine with the result given now

 

[Trebor76]

Try putting this from where the existing last For starts from:

For lngMyRow = lngEndRow To lngStartRow Step -1
        If Evaluate("SUMPRODUCT(--(A" & lngMyRow & ":D" & lngMyRow & "=""""))") > 0 Then
            wstConsTab.Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    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

It's late here in Oz so I won't get to check back in until tomorrow so good luck!!

Robert

 

[JRT2006]

That did it. it works perfectly now.....only thing I had to change was sort order. Ascending had it going from smallest to largest, descending had largest to smallest.

You sir are brilliant. Thank you, Have a good night!

 

[JRT2006]

Hey Robert, bot sure what happened as I was just cleaning up the worksheet. I was adding to formulas in my sheet to get rid of errors and I deleted a few unnecessary columns to clean the pages up (nothing that stood in the way of the data your macro was searching for.

When i run the macro now, i get a run-time error "13" type mismatch and this is highlighted in the code (in yellow)

If Evaluate("SUMPRODUCT(--(A" & lngMyRow & ":D" & lngMyRow & "=""""))") > 0 Then

I'm looking for what may have happened but I'm learning all this stuff as we speak, any clue?

 

[JRT2006]

only way i can figure out how to share the worksheet with you

Zippyshare.com - Draft Tool test.xlsm

 

[Trebor76]

There are two main issues:

• The location of the Final Grade column is not consistent between tabs ie for tab QB it's in column AC while for tab RB it's in column AM. As the macro looks at a particular columns it causes errors
• The macro tries to consolidate all tabs except for the consolidation tab. There is another tab called Team_Needs that it is also trying to consolidate but as it's totally different it's also causing issues.

If you can let me know what column Final Grade will be in and whether Team_Needs is the only tab to be ignored in the consolidation process I'll post an amended code.

Thanks,

Robert

 

[JRT2006]

'Final Grade' Can be in any column away from view. i was trying to push that column further back so the other users wont be tempted to look at it and/or change any of the formulas.

Team Needs is the only tab that should be ignored. There is also a hidden tab labeled 'Adj_Do_Not_Touch' i think. not sure if that matters or not.

I know, my skills are a mess, I've learned all i know from google and forums like this, but plan to go to school to learn the advanced stuff. Just a little intimidated is all

Thanks