Add a New WorkSheet in Excel – VBA Macro Code
This VBA code will add a new worksheet in your Excel project.
Sub AddNewWorksheet() Dim NewWorkSheet As Worksheet Set NewWorkSheet = Sheets.Add End Sub
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.
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
