Pivot Table - Easy way to add multiple Columns into Data secti
in [General]
|
Prev: Error Message - Input Range must be a contiguous Reference Help!!!
Next: changing your printer settings for custom paper size ?
From: Johnny_99 on 2 Jan 2010 14:04 Thanks Roger. I presume this is not a nonprogramming solution here? I'd love to simply select column 1, ctrl, select column n (and take the range of columns) and add them all ... perhaps asking too much? No prcedure or add-in solution? Thanks, "Roger Govier" wrote: > Hi Johnny > > The following code should get you started on what you want to do. > In this example the first column of source data is added to Page area, the > second column is added to the Row area then there is a loop to add 60 > columns to the Data area, ensuring that each is set to Sum and getting rid > of the annoying "Sum of " which has to appear before each field name, by > appending a space to the original Field name for use in the PT. > > Sub CreatePivot() > Dim wss As Worksheet, wsd As Worksheet > Dim i As Long, j As Long, fname As String > > Set wss = Sheets("Sheet1") > Application.ScreenUpdating = False > Application.Calculation = xlCalculationManual > > ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ > wss.Range("A1:CZ20000")).CreatePivotTable > TableDestination:="", TableName:= _ > "PivotTable1", > DefaultVersion:=xlPivotTableVersion10 > Set wsd = ActiveSheet > wsd.PivotTableWizard TableDestination:=wsd.Cells(3, 1) > > wsd.Cells(3, 1).Select > ' in this case the first 2 columns of source data have been added to > ' Row field and Page field respectively > wsd.PivotTables("PivotTable1").AddFields RowFields:=Array("Date", _ > "Data"), > PageFields:="Name" > > j = 2 ' set the start column as 1 less than where you wish to pick > ' up data fields from > For i = 1 To 60 > ' loop for 60 columns to add fields to the data area > ' picking up the field name from the column header > fname = wss.Cells(1, j + i).Value > With wsd.PivotTables("PivotTable1").PivotFields(fname) > .Orientation = xlDataField > .Function = xlSum ' force a Sum > .Name = fname & " " ' get rid of Sum of before field > name > ' by appending a space > to the source field Name > .Position = i > End With > Next > > ' next part allocates the 60 data fields across columns instead > ' of appearing under each other (if that is what is required) > > With ActiveSheet.PivotTables("PivotTable1").DataPivotField > .Orientation = xlColumnField > .Position = 1 > End With > > Application.ScreenUpdating = True > Application.Calculation = xlCalculationAutomatic > End Sub > > > -- > Regards > Roger Govier > > "Johnny_99" <Johnny99(a)discussions.microsoft.com> wrote in message > news:5B1D9D0B-B588-488F-BD2C-D6B10B1CEBEF(a)microsoft.com... > > As with a previous question, I have a large set of data (20,000 rows and > > about 100 coulmns). I wish to select about 60 columns to place in pivot > > "data" (along with others into "Rows"). > > > > Is there a way to select multiple columns (say all 60?) and move into > > "Data" > > in one step? Are there add-ins that help with this? Doing all 60 is > > possible > > but slow and somewhat error prone. > > > > Thanks in advance. > > > > __________ Information from ESET Smart Security, version of virus > > signature database 4738 (20100102) __________ > > > > The message was checked by ESET Smart Security. > > > > http://www.eset.com > > > > > > > > __________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > > . >
From: Roger Govier on 2 Jan 2010 18:13
Sorry, no such feature available. Code is the only way to automate the procedure -- Regards Roger Govier "Johnny_99" <Johnny99(a)discussions.microsoft.com> wrote in message news:4856A3B2-5389-4A5F-B18C-AB43BF54CA2E(a)microsoft.com... > Thanks Roger. > > I presume this is not a nonprogramming solution here? I'd love to simply > select column 1, ctrl, select column n (and take the range of columns) and > add them all ... perhaps asking too much? > > No prcedure or add-in solution? > > Thanks, > > "Roger Govier" wrote: > >> Hi Johnny >> >> The following code should get you started on what you want to do. >> In this example the first column of source data is added to Page area, >> the >> second column is added to the Row area then there is a loop to add 60 >> columns to the Data area, ensuring that each is set to Sum and getting >> rid >> of the annoying "Sum of " which has to appear before each field name, by >> appending a space to the original Field name for use in the PT. >> >> Sub CreatePivot() >> Dim wss As Worksheet, wsd As Worksheet >> Dim i As Long, j As Long, fname As String >> >> Set wss = Sheets("Sheet1") >> Application.ScreenUpdating = False >> Application.Calculation = xlCalculationManual >> >> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ >> >> wss.Range("A1:CZ20000")).CreatePivotTable >> TableDestination:="", TableName:= _ >> "PivotTable1", >> DefaultVersion:=xlPivotTableVersion10 >> Set wsd = ActiveSheet >> wsd.PivotTableWizard TableDestination:=wsd.Cells(3, 1) >> >> wsd.Cells(3, 1).Select >> ' in this case the first 2 columns of source data have been added to >> ' Row field and Page field respectively >> wsd.PivotTables("PivotTable1").AddFields RowFields:=Array("Date", _ >> "Data"), >> PageFields:="Name" >> >> j = 2 ' set the start column as 1 less than where you wish to pick >> ' up data fields from >> For i = 1 To 60 >> ' loop for 60 columns to add fields to the data area >> ' picking up the field name from the column header >> fname = wss.Cells(1, j + i).Value >> With wsd.PivotTables("PivotTable1").PivotFields(fname) >> .Orientation = xlDataField >> .Function = xlSum ' force a Sum >> .Name = fname & " " ' get rid of Sum of before field >> name >> ' by appending a >> space >> to the source field Name >> .Position = i >> End With >> Next >> >> ' next part allocates the 60 data fields across columns instead >> ' of appearing under each other (if that is what is required) >> >> With ActiveSheet.PivotTables("PivotTable1").DataPivotField >> .Orientation = xlColumnField >> .Position = 1 >> End With >> >> Application.ScreenUpdating = True >> Application.Calculation = xlCalculationAutomatic >> End Sub >> >> >> -- >> Regards >> Roger Govier >> >> "Johnny_99" <Johnny99(a)discussions.microsoft.com> wrote in message >> news:5B1D9D0B-B588-488F-BD2C-D6B10B1CEBEF(a)microsoft.com... >> > As with a previous question, I have a large set of data (20,000 rows >> > and >> > about 100 coulmns). I wish to select about 60 columns to place in pivot >> > "data" (along with others into "Rows"). >> > >> > Is there a way to select multiple columns (say all 60?) and move into >> > "Data" >> > in one step? Are there add-ins that help with this? Doing all 60 is >> > possible >> > but slow and somewhat error prone. >> > >> > Thanks in advance. >> > >> > __________ Information from ESET Smart Security, version of virus >> > signature database 4738 (20100102) __________ >> > >> > The message was checked by ESET Smart Security. >> > >> > http://www.eset.com >> > >> > >> > >> >> __________ Information from ESET Smart Security, version of virus >> signature database 4738 (20100102) __________ >> >> The message was checked by ESET Smart Security. >> >> http://www.eset.com >> >> >> >> . >> > > __________ Information from ESET Smart Security, version of virus > signature database 4738 (20100102) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________ The message was checked by ESET Smart Security. http://www.eset.com |