Pivot date, that's all

[Greg Snidow]

Greetings all. I have a frequent need to simply pivot a table, and that's
it. I end up using the pivot table wizard, and then trying to copy and paste
the values I need, and it is a time consuming process. All I really need is
to simply pivot it, turning the rows into columns, and visa versa. I made a
simple macro to do the task, but in the interest of learning something new, I
would like to see if anyone else has a better way. Take any table of data,
and highlight the cell between the row header and column header, and run it.
It will produce a pivoted version of the table under the original table. So,
for example, if your table column header is in row 1, and your row labels are
in column A, you would want to select A1, and run the macro. Any ideas on a
better way? Any reason why I should not use this? Thanks in advance, if
anyone is so inclined to give some feedback.

Sub Pivot()

Dim StartCell As Variant
Dim StartCellOld As Variant
Dim r As Integer 'will hold number of rows in the table
Dim rOld As Integer
Dim c As Integer 'will hold number of columns in the table
Dim i As Integer

'Pick a starting point as reference to be used later
StartCell = ActiveCell.Address
StartCellOld = StartCell 'save this to return to the starting point

'Start by counting the rows
r = 0
Range(StartCell).Offset(1, 0).Activate
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Activate
r = r + 1
Loop

'Then count the columns
Range(StartCell).Activate
Range(StartCell).Offset(0, 1).Activate

c = 0

Do While ActiveCell.Value <> ""
ActiveCell.Offset(0, 1).Activate
c = c + 1
Loop

Range(StartCell).Activate

'Populate the new row labels
Range(StartCell).Offset(r + 4, 0).Activate
i = 1
Do While c >= 0
ActiveCell.Value = Range(StartCell).Offset(0, i).Value
c = c - 1
i = i + 1
ActiveCell.Offset(1, 0).Activate
Loop
'Then populate the column headers and data
Range(StartCell).Offset(r + 3, 1).Activate
i = 1
rOld = r

Do Until Range(StartCell).Offset(r, 0) = ""
Do Until r = 0
ActiveCell.Value = Range(StartCell).Offset(i, 0).Value
r = r - 1
i = i + 1
ActiveCell.Offset(0, 1).Activate
Loop
StartCell = Range(StartCell).Offset(0, 1).Address
r = rOld
i = 1
ActiveCell.Offset(1, -r).Activate
ActiveCell.Value = Range(StartCell).Offset(i, 0).Value
Loop

Range(StartCellOld).Offset(r + 3, 0).Activate


End Sub

 

[Per Jessen]

Hi

Why not use Transpose.

Select the table, and copy it, select destination cell, goto Insert >
PasteSpecial > Transpose > OK

Regards,
Per

 

[ryguy7272]

I always use this to get to the end of the entire used range to build the
Pivot Table:
Notice, the data is on "Sheet1", and I use
..Range("A1").CurrentRegion).

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:=Sheets("Sheet2").Range("A3"),
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

HTH,
Ryan---

 

[Greg Snidow]

Dude, thank you so much. The whole time I was writing my macro, I was
thinking "I know there is a function for this", but my search for pivoting
brought me only to information about making pivot tables for aggregation and
stuff. Anyhow, it was mostly an exercise for me, since I am still learning.
I think I'll continue to do it my way, just for the feeling of
accomplishment, or maybe try to incorportate what ryguy7272 has suggested.
Anyhow, thanks so much for the quick reply, and the tip on a very useful
function.

Greg

 

[Greg Snidow]

I'm still trying to figure out how to get this to work. Is this just part of
another macro? I don't understand what .Range("A1").CurrentRegion). means,
nor how to use it. I have seen this type of line here often, but it is
usually preceded by 'With' at some point, and admitedly, my delving into
macros has not gone past Do loops and using counters yet, as you can see from
my code above. Also, is the first line, Pivot Table:, supposed to be part of
the code? If so, is it declaring an object or variable? Or somehow setting
the stage for the rest? Thanks for the help, I'll be sure to post back if I
have any break throughs with it.

Greg

 

[Per Jessen]

Hi

The current region is a range bounded by any combination of blank rows
and blank columns.
As per ýour code you could use this:

'Pick a starting point as reference to be used later
Dim StartCell as Range
Set StartCell = ActiveCell

'Start by counting the rows
r = StartCell.CurrentRegion.Rows.Count

'Then count the columns
c = StartCell.CurrentRegion.Columns.Count

The first line in Ryan's code is Range("A1")...


Also try this and see what happens:

Range("A1").CurrentRegion.Select' Change A1 to be a cell in your table


Hopes this helps

 

[Greg Snidow]

Per, thanks for the tip on using range counts, that is certainly much easier
than using a loop. I still can not figure out what Ryan's code is doing
though. I can follow the logic, in that it seems to be creating a source and
destination table, and pivoting, but it will not work no matter how I change
it.