Try this:

Sub Consolidate_Data()
Dim arr As Variant
Dim wsMain As Worksheet:   Set wsMain = Sheets("Consolidated")
Dim ws As Worksheet
Dim i As Integer
Dim r As Range
Dim lc As Long, lr As Long

Application.ScreenUpdating = False

arr = Array("Full Name2", "Office Location", "Grand Total")

For Each ws In Worksheets
    If ws.Name <> wsMain.Name Then
        lc = ws.Cells(7, Columns.Count).End(xlToLeft).Column
        For i = LBound(arr) To UBound(arr)
            Set r = ws.Range(ws.Cells(7, 1), ws.Cells(7, lc)).Find(arr(i), , xlValues, xlWhole)
            If Not r Is Nothing Then
                lr = ws.Cells(Rows.Count, r.Column).End(xlUp).Row
                ws.Range(ws.Cells(8, r.Column), ws.Cells(lr, r.Column)).Copy wsMain.Cells(Rows.Count, i + 1).End(3)(2)
            End If
        Next i
    End If
Next ws

Application.ScreenUpdating = True
  
End Sub
Note: You didn't mention combining names and locations so I just simply followed your directions to the letter