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

JRT2006 · Sep 23, 2014

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 · Sep 23, 2014

Hi JRT2006:

Try this:

Code:

Option Explicit
Sub Macro2()

    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
    Dim wstMyTab As Worksheet, _
        wstConsTab As Worksheet
        
    Application.ScreenUpdating = False
    
    Set wstConsTab = ThisWorkbook.Sheets("Big Board") ' 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.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Assumes the required data is across Col's A to D. Change if required.
                If lngEndRow >= lngStartRow Then
                    lngPasteRow = wstConsTab.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 'Assumes the required data is across Col's A to D. Change if required.
                    If lngPasteRow = 0 Then
                        lngPasteRow = lngStartRow
                    End If
                    wstMyTab.Range("A" & lngStartRow & ":D" & lngEndRow).Copy Destination:=wstConsTab.Range("A" & lngPasteRow)
                End If
            On Error GoTo 0
        End If
    Next wstMyTab
    
    Set wstConsTab = Nothing
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

JRT2006 · Sep 23, 2014

Thanks for the reply Roger, few questions.

Do I just input this code through vba on the sheet I want to have the information on? Code is where the real beginner side of me comes out.

Also, "'Assumes the required data is across Col's A to D. Change if required." The actual data is on columns A, D, and AY. how do i refer to those only?

Trebor76 · Sep 23, 2014

Do I just input this code through vba on the sheet I want to have the information on?

No, it goes into a standard module. To do this follow these 5 steps:

1. Copy my code to the clipboard (Crtl + C)
2. Open the Visual Basic Editor (VBE) by pressing Alt + F11
3. From the Insert menu select Module
4. Paste (Ctrl + V) my code from step 1 into the blank white module
5. Exit the VBE by clicking the Close and Return to Microsoft Excel option from the File menu

You now can run the macro whenever you like by pressing Alt + F8 and from the Macro dialog click on Macro1 and then click Run

Also, "'Assumes the required data is across Col's A to D. Change if required." The actual data is on columns A, D, and AY. how do i refer to those only?

In your original post you said there was 4 fields? Also, do you want these columns to go into the same columns of the "Big Board" tab or in sequential columns ie A, B and C?

Thanks,

Robert (aka Roger

)

JRT2006 · Sep 23, 2014

Ha Ha, my apologies on the name change.

The original sheets have 19 columns of information, of those 19 columns I am only wanting to use the information of 4 columns. Column A (which is actually A, B, and C merged) holds the players name. Column D holds the projected round. CELL E1 holds the player position (I used one cell to label position instead of having the same info repeated down the column). Column AY holds the Final Grade

Id like for them to be sequential; Player Name, Position, Projected Round, and Final Grade (A, E1, D, AY)

Thanks again ROBERT

Trebor76 · Sep 23, 2014

Merged cells can cause issues with macros. That said see how this goes:

Code:

Option Explicit
Sub Macro3()

    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
    Dim wstMyTab As Worksheet, _
        wstConsTab As Worksheet
        
    Application.ScreenUpdating = False
    
    Set wstConsTab = ThisWorkbook.Sheets("Big Board") ' 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 > 0 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
                    wstConsTab.Range("A" & lngPasteRow).Value = wstMyTab.Range("A" & lngEndRow).Value 'Put the last entry in Col. A of the football position tab into the next available row in Col. A of the consolidated tab. Change if required.
                    wstConsTab.Range("B" & lngPasteRow).Value = wstMyTab.Range("E1").Value 'Put the entry in cell E1 of the football position tab into the next available row in Col. B of the consolidated tab. Change if required.
                    wstConsTab.Range("C" & lngPasteRow).Value = wstMyTab.Range("D" & lngEndRow).Value 'Put the last entry in Col. D of the football position tab into the next available row in Col. C of the consolidated tab. Change if required.
                    wstConsTab.Range("D" & lngPasteRow).Value = wstMyTab.Range("AY" & lngEndRow).Value 'Put the last entry in Col. AY of the football position tab into the next available row in Col. D of the consolidated tab. Change if required.
                End If
            On Error GoTo 0
        End If
    Next wstMyTab
    
    Set wstConsTab = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Data has now been consolidated.", vbInformation

End Sub

HTH

Robert

JRT2006 · Sep 23, 2014

i dont know how you all come up with this stuff, so confusing. I ran the macro, it pulls information from the sheets, but it doesnt seem to be gathering all information.
It grabs one row of information from each sheet.

Maybe that was something I didnt explain fully, if so im sorry. Each position tab can have up to 39 rows of player information, but most likely wont go over 10therefor leaving some of the rows blank. Rows 2 through 40 are reserved for information. The macro seems to be pulling information from the middle rows rather than rows 2 through 40 on each page.
When I run the macro, Player Name is left blank because I am assuming it is pulling only information from one of the empty cells, the positions shows up fine, projected round and draft grade are blank because those are empty too.
Not exactly sure which row the data is being pulled from because so many are empty, but I'm tring to get it to grab all the information from the sheets, Column A Row 2 through 40, Column D Row 2 through 40, Cell E1, and column AY Row 2 through 40.

From there, I can go ahead and use the Data>Sort to sort the players based on Column D (on the Big Board sheet, from largest to smallest)

Player Name	Position	Proj. Round	Draft Value
	QB		41.25
	RB		37.35
	WR		36
	TE		39.23
	LT		50
	LG		50
	C		50
	RG		50
	RT		50
	DE		38.75
	DT		41.25
	OLB		35
	ILB		39.23
	CB		37.5
	FS		37.5
	SS		37.5

JRT2006 · Sep 23, 2014

Its pulling the data from row 40 of each tab (the last row that may have information inputted), but not rows 2 through 39. I adjusted all the grades on the QB tab and ran the macro, the information matched.

Trebor76 · Sep 24, 2014

OK try this - you will have adjust the lines of code under the //This is where the data from the individual sheets are being consolidated to the 'wstConsTab' sheet. Change as required.// heading if required as you know your data obviously better than I do:

Code:

Option Explicit
Sub Macro4()

    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
    Dim wstMyTab As Worksheet, _
        wstConsTab As Worksheet
        
    Application.ScreenUpdating = False
    
    Set wstConsTab = ThisWorkbook.Sheets("Big Board") ' 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.//
                    wstConsTab.Range("A" & lngPasteRow).Value = wstMyTab.Range("E1").Value
                    wstMyTab.Range("B" & lngStartRow & ":B" & lngEndRow).Copy wstConsTab.Range("B" & lngPasteRow)
                    wstMyTab.Range("D" & lngStartRow & ":D" & lngEndRow).Copy wstConsTab.Range("C" & lngPasteRow)
                    wstMyTab.Range("AY" & lngStartRow & ":AY" & lngEndRow).Copy wstConsTab.Range("D" & lngPasteRow)
                End If
            On Error GoTo 0
        End If
    Next wstMyTab
    
    Set wstConsTab = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Data has now been consolidated.", vbInformation

End Sub

Robert

JRT2006 · Sep 24, 2014

Wonderful, this is doing what I need.
Few questions, the data being pulled from column AY returns as #REF. There is a simple formula in Column AY, just two cells multiplied.
Column B only populates the first row with data and leaves the rest blank, the rest of the columns don't do that.
Last, is it possible to omit the rows/columns that don't return any information? Blank cells?

I added a few columns to the sheets, and adjusted as you said. Here is the adjusted code if it makes it any easier to work with.

Code:

Sub Macro4()

    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
    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.//
                    wstConsTab.Range("B" & lngPasteRow).Value = wstMyTab.Range("E1").Value
                    wstMyTab.Range("A" & lngStartRow & ":A" & lngEndRow).Copy wstConsTab.Range("A" & lngPasteRow)
                    wstMyTab.Range("D" & lngStartRow & ":D" & lngEndRow).Copy wstConsTab.Range("C" & lngPasteRow)
                    wstMyTab.Range("BA" & lngStartRow & ":BA" & lngEndRow).Copy wstConsTab.Range("D" & lngPasteRow)
                End If
            On Error GoTo 0
        End If
    Next wstMyTab
    
    Set wstConsTab = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Data has now been consolidated.", vbInformation


End Sub

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

JRT2006

New Member

Excel Facts

Trebor76

Well-known Member

JRT2006

New Member

Trebor76

Well-known Member

JRT2006

New Member

Trebor76

Well-known Member

JRT2006

New Member

JRT2006

New Member

Trebor76

Well-known Member

JRT2006

New Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock