-----------------------------------------------------
VB behind each worksheet/tab containing a table set up as a grid to be un-pivoted for inclusion as normalized data in SQL queries:

Option Explicit

Private Sub CommandButton1_Click()
    unPivot
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Module1.TableUpdate Target:=Target
    
End Sub

Private Sub unPivot()
'Copyright (c) 2017 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice.

    Dim tblSourceUnpivotName As String, tabTargetUnpivotName As String, tblTargetUnpivotName As String
    Dim colElementItemName As String, colElementValueName As String, MessageBarColRptName As String
    Dim thisWorkbook As Workbook
    Dim thisWorksheet As Worksheet
    Dim thisWorksheetName As String
    Dim rngCellInTblTarget As Range
    
    'Set up workbook and worksheet references
    Set thisWorkbook = module1.myworkbook
    Set thisWorksheet = ActiveSheet
    
'    'Make reference to thisWorksheet by looping through worksheets until we find tab named AllocationTable
'    For Each thisWorksheet In thisWorkbook.Sheets   'Find worksheet "Allocation Table" for source data
'        If thisWorksheet.Name = "AllocationTable" Then Exit For
'    Next thisWorksheet
    
    'Catch unpivot variables specified on source worksheet
    tblSourceUnpivotName = thisWorksheet.Cells.Range("tblSourceUnpivotName")
    tabTargetUnpivotName = thisWorksheet.Cells.Range("tabTargetUnpivotName")
    tblTargetUnpivotName = thisWorksheet.Cells.Range("tblTargetUnpivotName")
    colElementItemName = thisWorksheet.Cells.Range("colElementItemName")
    colElementValueName = thisWorksheet.Cells.Range("colElementValueName")
    MessageBarColRptName = thisWorksheet.Cells.Range("MessageBarColRpt")
    
    'Call the unpivot / Do the work ! passing in the params picked up just above
    Module1.unPivot tblSourceUnpivotName:=tblSourceUnpivotName, _
                    tabTargetUnpivotName:=tabTargetUnpivotName, _
                    tblTargetUnpivotName:=tblTargetUnpivotName, _
                    thisWorksheet:=thisWorksheet, _
                    colElementItemName:=colElementItemName, _
                    colElementValueName:=colElementValueName, _
                    MessageBarColRpt:=MessageBarColRptName
    
    'Make a named range that can be used to export the UnPivot table to a Database such as MSAccess or SQLserver
    'First identify a cell in tblTargetUnpivot
    If Module1.nameExists(tblTargetUnpivotName, thisWorkbook.Worksheets(tabTargetUnpivotName).ListObjects) Then
        Set rngCellInTblTarget = thisWorkbook.Worksheets(tabTargetUnpivotName).ListObjects(tblTargetUnpivotName).Range.Cells(2, 1)
    
    End If
    'Then call TableUpdate to make and name the Range of the table so that it will be visible to Access and SQLserver.
    Module1.TableUpdate Target:=rngCellInTblTarget
    
    'The Unpivot commmand button is an OLE form object which may wander on the page; after each use set up its size and position.
    thisWorksheet.OLEObjects("CommandButton1").Width = thisWorksheet.Range(Cells(2, 4), Cells(2, 5)).Width '111
    thisWorksheet.OLEObjects("CommandButton1").Height = thisWorksheet.Range(Cells(2, 4), Cells(3, 5)).Height '28
    thisWorksheet.OLEObjects("CommandButton1").Top = thisWorksheet.Cells(2, 4).Top   '16
    thisWorksheet.OLEObjects("CommandButton1").Left = thisWorksheet.Cells(2, 4).Left    '302
    
    'Mark tblTargetUnpivot as Dirty so pivot reports will update
    Worksheet_SelectionChange Target:=rngCellInTblTarget 'Mark tblTargetUnpivot as Dirty so pivot reports will update
   

End Sub