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