Convert CrossTab Table to Database List Table – Excel VBA Macro Code
If you work with databases and regular data tables you probably had a situation when someone gave you a regular Excel 2 dimensional spreadsheet that is not that useful if you need to use the data within a database and run some queries or just make PivotTables in Excel. The Macro code below will transform your CrossTab table to a regular database list table.
This script will create a new sheet and prompt you to enter a starting point for your converted data. Then it will render the data in 3 columns as a regular database data table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sub CrossTabToDatabase() Dim DataTable As Range, OutputRange As Range Dim RowOutput As Long Dim r As Long, c As Long Dim WS As Worksheet On Error Resume Next Set DataTable = ActiveCell.CurrentRegion If DataTable.Count = 1 Or DataTable.Rows.Count < 3 Then MsgBox "Select a cell within the summary table", vbCritical Exit Sub End If DataTable.Select Set WS = Sheets.Add Set OutputRange = Application.InputBox(prompt:="Select a cell starting where you'd like to output the new datatable.", Type:=8) ' Convert the range RowOutput = 2 Application.ScreenUpdating = False OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3") For r = 2 To DataTable.Rows.Count For c = 2 To DataTable.Columns.Count OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1) OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c) OutputRange.Cells(RowOutput, 3) = DataTable.Cells(r, c) OutputRange.Cells(RowOutput, 3).NumberFormat = DataTable.Cells(r, c).NumberFormat RowOutput = RowOutput + 1 Next c Next r End Sub |
Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/
