| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot date, that's all
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot date, that's all
Hi
Why not use Transpose. Select the table, and copy it, select destination cell, goto Insert PasteSpecial Transpose OK Regards, Per On 18 Mar., 01:25, Greg Snidow wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot date, that's all
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:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _ TableDestination:=Sheets("Sheet2").Range("A3"), TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 HTH, Ryan--- -- RyGuy "Greg Snidow" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot date, that's all
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 "Per Jessen" wrote: Hi Why not use Transpose. Select the table, and copy it, select destination cell, goto Insert PasteSpecial Transpose OK Regards, Per On 18 Mar., 01:25, Greg Snidow wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot date, that's all
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 "ryguy7272" wrote: 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:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _ TableDestination:=Sheets("Sheet2").Range("A3"), TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 HTH, Ryan--- -- RyGuy "Greg Snidow" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot date, that's all
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 --- Per On 18 Mar., 03:40, Greg Snidow wrote: 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 "ryguy7272" wrote: 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:=xlDatab ase, SourceData:= _ * * Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _ * * TableDestination:=Sheets("Sheet2").Range("A3"), TableName:="PivotTable1", _ * * DefaultVersion:=xlPivotTableVersion10 HTH, Ryan--- -- RyGuy "Greg Snidow" wrote: 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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot date, that's all
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. "Per Jessen" wrote: 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 --- Per On 18 Mar., 03:40, Greg Snidow wrote: 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 "ryguy7272" wrote: 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:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _ TableDestination:=Sheets("Sheet2").Range("A3"), TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 HTH, Ryan--- -- RyGuy "Greg Snidow" wrote: 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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| pivot with calculated date | Charts and Charting in Excel | |||
| Pivot Table sorts date as Alpha, not as Date | Excel Worksheet Functions | |||
| Start Date And End Date with pivot tables? | Excel Programming | |||
| PIVOT TABLE DATE | Excel Discussion (Misc queries) | |||
| Pivot date grouping | Excel Discussion (Misc queries) | |||
Linear Mode
