software

I have a table of quarterly shipments by vendor/product and want to convert
this to a pivot table. Right now I have 12 quaters of data, with includes 8
quaters of actual data and 4 of estimates. If I get how pivot tables work, I
would need to add 12 rows for each vendor-product combination and have one
column that includes quot;data.quot; Is there an automated way to convert an excel
table, where data is in multiple columns, into a pivot table? And then update
each quarter.

Please tell me if I am going about this the wrong way. I have only used,
never set up, pivot tables,

Ellen

Ellen,

Select one cell in your cross-tab data table, and run the macro below. It will create a new sheet
with a database table suitable for use with pivot tables.

HTH,
Bernie
MS Excel MVP

Sub MakeTable2()
Dim myCell As Range
Dim newSheet As Worksheet
Dim mySheet As Worksheet
Dim i As Long
Dim j As Integer
Dim k As Long
Dim l As Integer
Dim mySelection As Range
Dim RowFields As Integer

Set mySheet = ActiveSheet
Set mySelection = ActiveCell.CurrentRegion
RowFields = Application.InputBox( _
quot;How many left-most columns to treat as row fields?quot;, _
quot;CrossTab to DataBase Helperquot;, 1, , , , , 1)
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(quot;New Databasequot;).Delete
Application.DisplayAlerts = True
Set newSheet = Worksheets.Add
newSheet.Name = quot;New Databasequot;
mySheet.Activate
i = 1
For j = mySelection(1).Row 1 To _
mySelection(mySelection.Cells.Count).Row
For k = mySelection(1).Column RowFields To _
mySelection(mySelection.Cells.Count).Column
If mySheet.Cells(j, k).Value lt;gt; quot;quot; Then
For l = 1 To RowFields
newSheet.Cells(i, l).Value = _
Cells(j, mySelection(l).Column).Value
Next l
newSheet.Cells(i, RowFields 1).Value = _
Cells(mySelection(1).Row, k).Value
newSheet.Cells(i, RowFields 2).Value = _
Cells(j, k).Value
i = i 1
End If
Next k
Next j

End Sub

quot;Pivot Table Creation Helpquot; lt;Pivot Table Creation gt; wrote in message
...
gt;I have a table of quarterly shipments by vendor/product and want to convert
gt; this to a pivot table. Right now I have 12 quaters of data, with includes 8
gt; quaters of actual data and 4 of estimates. If I get how pivot tables work, I
gt; would need to add 12 rows for each vendor-product combination and have one
gt; column that includes quot;data.quot; Is there an automated way to convert an excel
gt; table, where data is in multiple columns, into a pivot table? And then update
gt; each quarter.
gt;
gt; Please tell me if I am going about this the wrong way. I have only used,
gt; never set up, pivot tables,
gt;
gt; Ellen