Combine multiple tabs based on column header.

[Cldbrooks15]

I have an excel workbook with 20 different tabs that contain data in each column with the last column of each tab with the year-to-date (YTD) information. The header information starts on row 4 in each tab but the YTD tab is not on the same column (It could be in column J for one tab but column Q for another tab). How can I consolidate the information to pull the YTD information for each tab?

 

[Larry Haydn]

Please show us how your workbook is set up, then we will have a better idea of how to help.

 

[Cldbrooks15]

 

[Larry Haydn]

Thank you.
This is how I will solve this problem:
Setting up the workbook
1. I hate to use the R1C1 method, so I set up a worksheet to loop through the column name.
To use this list to find a column name, run a loop from row 2 to row x, and get the column name in column B.

2. Set up the data worksheets "100", "200", etc.
3. Set up a Consolidated worksheet to paste the TYD columns

Copying the Data
Run a loop to go through each worksheet (except the "Consolidated" worksheet)
Run a loop through the "ColRef" worksheet to get a column name
In the current data worksheet, at row 4, check each column to find "YTD"
When "YTD" is found, copy the column to "Consolidated" worksheet
Until no more worksheets to check

The Code

Sub CopyFromDiffColumn()
'====================================================
'   Copy YTD Column from all tabs
'   where YTD may appear in different column
'
'   Worksheet Names:
'      Column Reference      : ColRef
'      Data Worksheets       : 100, 200, 300, etc.
'      Consolidated worksheet: Consolidated
'====================================================
Dim WB As Workbook      'This workbook
Dim frWS As Worksheet   'Copy-from worksheet
Dim toWS As Worksheet   'Paste-to worksheet
Dim refWS As Worksheet  'Column reference worksheet
Dim ws As Object        'worksheet object in the Worksheets Collection

Dim hRow As Long        'Heading row
Dim RowNo As Long       'row counter

Dim ColNo As Integer    'Column number
Dim ColNam As String    'Column name

    '=====================================================
    '   Identify the workbook and destination worksheet
    '=====================================================
    Set WB = ActiveWorkbook
    Set toWS = WB.Sheets("Consolidated")
    Set refWS = WB.Sheets("ColRef")
    '=======================================
    '   Initialize the heading row number
    '=======================================
    hRow = 4
    '===================================================
    '   Loop through the worksheets
    '   except the Consolidated and ColRef worksheets
    '===================================================
    For Each ws In Worksheets
        If (ws.Name <> "Consolidated") And _
           (ws.Name <> "ColRef") Then
            Debug.Print "Checking worksheet '" & ws.Name & "'"
            '=============================
            '   Identify this worksheet
            '=============================
            Set frWS = WB.Sheets(ws.Name)
            '==========================================================
            '   Use the ColRef worksheet to find the YTD column name
            '   Begin from row 2, max row 27 (Col A..Z)
            '==========================================================
            For RowNo = 2 To 27
                '===============================================
                '   Get the column name from ColRef worksheet
                '===============================================
                ColNam = refWS.Range("B" & RowNo)
                    '====================================
                    '   If this column header is "YTD"
                    '====================================
                    If frWS.Range(ColNam & hRow) = "YTD" Then
                        Debug.Print "Found YTD in worksheet '" & ws.Name & "', Col " & ColNam
                        '================================================
                        '   Here is where you copy the YTD column data
                        '   to the Colsolidated worksheet
                        '================================================
                        
                        ' Here you specify how you will copy this column from this worksheet
                        ' and how you will paste the data in Colsolidated worksheet.
                        ' For example, you can use another variable to get the column name
                        ' from the ColRef worksheet to control which column to paste the data.
                        
                        Exit For
                    End If
            
            Next
            
        End If
    Next

End Sub

Here is the result when I run the code - which just finds the column, but not actually copying the data:

And shows that the program has found the correct column: