Pivot Table - Easy way to add multiple Columns into Data section?

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.
0
Utf
1/2/2010 3:30:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
2025 Views

Similar Articles

[PageSpeed] 24

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@discussions.microsoft.com> wrote in message 
news:5B1D9D0B-B588-488F-BD2C-D6B10B1CEBEF@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



0
Roger
1/2/2010 5:32:54 PM
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@discussions.microsoft.com> wrote in message 
> news:5B1D9D0B-B588-488F-BD2C-D6B10B1CEBEF@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
> 
> 
> 
> .
> 
0
Utf
1/2/2010 7:04:01 PM
Sorry, no such feature available.
Code is the only way to automate the procedure

-- 
Regards
Roger Govier

"Johnny_99" <Johnny99@discussions.microsoft.com> wrote in message 
news:4856A3B2-5389-4A5F-B18C-AB43BF54CA2E@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@discussions.microsoft.com> wrote in message
>> news:5B1D9D0B-B588-488F-BD2C-D6B10B1CEBEF@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



0
Roger
1/2/2010 11:13:50 PM
Is the code VBA?
It does not seem to compile in VBA.  The :=3D outside the scope of a
paramter appears to fail
e.g. using TableDestination:=3D"" as an assignment.
Sorry is this is a silly question.  Tom

On Jan 2, 6:13=A0pm, "Roger Govier" <roger@technology4unospamdotcodotuk>
wrote:
> Sorry, no such feature available.
> Code is the only way to automate the procedure
>
> --
> Regards
> Roger Govier
>
> "Johnny_99" <Johnn...@discussions.microsoft.com> wrote in message
>
> news:4856A3B2-5389-4A5F-B18C-AB43BF54CA2E@microsoft.com...
>
>
>
>
>
> > Thanks Roger.
>
> > I presume this is not a nonprogramming solution here? I'd love to simpl=
y
> > 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()
> >> =A0 =A0 Dim wss As Worksheet, wsd As Worksheet
> >> =A0 =A0 Dim i As Long, j As Long, fname As String
>
> >> =A0 =A0 Set wss =3D Sheets("Sheet1")
> >> =A0 =A0 Application.ScreenUpdating =3D False
> >> =A0 =A0 Application.Calculation =3D xlCalculationManual
>
> >> =A0 =A0 ActiveWorkbook.PivotCaches.Add(SourceType:=3DxlDatabase, Sourc=
eData:=3D _
>
> >> wss.Range("A1:CZ20000")).CreatePivotTable
> >> TableDestination:=3D"", TableName:=3D _
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0"PivotTable1",
> >> DefaultVersion:=3DxlPivotTableVersion10
> >> =A0 =A0 Set wsd =3D ActiveSheet
> >> =A0 =A0 wsd.PivotTableWizard TableDestination:=3Dwsd.Cells(3, 1)
>
> >> =A0 =A0 wsd.Cells(3, 1).Select
> >> =A0 =A0 ' in this case the first 2 columns of source data have been ad=
ded to
> >> =A0 =A0 ' Row field and Page field respectively
> >> =A0 =A0 wsd.PivotTables("PivotTable1").AddFields RowFields:=3DArray("D=
ate", _
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "Data"),
> >> PageFields:=3D"Name"
>
> >> =A0 =A0 j =3D 2 =A0' set the start column as 1 less than where you wis=
h to pick
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0' up data fields from
> >> =A0 =A0 For i =3D 1 To 60
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ' loop for 60 columns to add fields to=
 the data area
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ' picking up the field name from the c=
olumn header
> >> =A0 =A0 =A0 =A0 fname =3D wss.Cells(1, j + i).Value
> >> =A0 =A0 =A0 =A0 With wsd.PivotTables("PivotTable1").PivotFields(fname)
> >> =A0 =A0 =A0 =A0 =A0 =A0 .Orientation =3D xlDataField
> >> =A0 =A0 =A0 =A0 =A0 =A0 .Function =3D xlSum =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 ' force a Sum
> >> =A0 =A0 =A0 =A0 =A0 =A0 .Name =3D fname & " " =A0 =A0 =A0 =A0 =A0' get=
 rid of Sum of before field
> >> name
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ' by appending a
> >> space
> >> to the source field Name
> >> =A0 =A0 =A0 =A0 =A0 =A0 .Position =3D i
> >> =A0 =A0 =A0 =A0 End With
> >> =A0 =A0 Next
>
> >> =A0 =A0 ' next part allocates the 60 data fields across columns instea=
d
> >> =A0 =A0 ' of appearing under each other (if that is what is required)
>
> >> =A0 =A0 With ActiveSheet.PivotTables("PivotTable1").DataPivotField
> >> =A0 =A0 =A0 =A0 .Orientation =3D xlColumnField
> >> =A0 =A0 =A0 =A0 .Position =3D 1
> >> =A0 =A0 End With
>
> >> =A0 =A0 Application.ScreenUpdating =3D True
> >> =A0 =A0 Application.Calculation =3D xlCalculationAutomatic
> >> End Sub
>
> >> --
> >> Regards
> >> Roger Govier
>
> >> "Johnny_99" <Johnn...@discussions.microsoft.com> wrote in message
> >>news:5B1D9D0B-B588-488F-BD2C-D6B10B1CEBEF@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 pi=
vot
> >> > "data" (along with others into "Rows").
>
> >> > Is there a way to select multiple columns (say all 60?) and move int=
o
> >> > "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 signatu=
re database 4738 (20100102) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com- Hide quoted text -
>
> - Show quoted text -

0
tlee
1/29/2010 3:43:05 PM
Hi Tom

Yes the code is written in VBA
I suspect that line wrap in your Newsreader is causing the problem.

I have reproduced the code below, with more forced line breaks  so hopefully 
your newsreader won't mess it up.

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
           ' get rid of Sum of before field name
         ' by adding a space to the source field Name
             .Name = fname & " "
            .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

"tlee" <leethomasy2@gmail.com> wrote in message 
news:e9cda5ad-d299-41c1-93bc-ed9665029293@a17g2000pre.googlegroups.com...
> Is the code VBA?
> It does not seem to compile in VBA.  The := outside the scope of a
> paramter appears to fail
> e.g. using TableDestination:="" as an assignment.
> Sorry is this is a silly question.  Tom
>
> On Jan 2, 6:13 pm, "Roger Govier" <roger@technology4unospamdotcodotuk>
> wrote:
>> Sorry, no such feature available.
>> Code is the only way to automate the procedure
>>
>> --
>> Regards
>> Roger Govier
>>
>> "Johnny_99" <Johnn...@discussions.microsoft.com> wrote in message
>>
>> news:4856A3B2-5389-4A5F-B18C-AB43BF54CA2E@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" <Johnn...@discussions.microsoft.com> wrote in message
>> >>news:5B1D9D0B-B588-488F-BD2C-D6B10B1CEBEF@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- Hide quoted text -
>>
>> - Show quoted text -
>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4821 (20100130) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
1/31/2010 10:36:31 AM
Reply:

Similar Artilces:

Validating Data on a Form with a subForm
I have a form with subforms with data validation in the main Form's Before_Update Event. The problem that I am encountering is when the user has not updated all required data but enters the subform the Form Before_Update Event fires and the validation will fail. Is there a way to know what the next object has been selected might be or how I would not perform the Form's validation when the subform has been selected? Any help will be appreciated!!! No. Access saves the main form's record at the point when you move from the main form into the subform. Any record that fails v...

sendkeys action requires utility add in access 2003
I Know that this has been asked before. My question is why it will not pop up on one computer but will on another. The same actions being performed. Any input will be helpful. Thanks Craig "Craig" <Craig@discussions.microsoft.com> wrote in message news:5A3A76CB-9556-4B9D-B85E-39F13771D0C8@microsoft.com... >I Know that this has been asked before. My question is why it will not >pop > up on one computer but will on another. The same actions being performed. > Any input will be helpful. > > Thanks Craig You'll have to give a few more details a...

Numbers dont display or add up correctly in excel
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I type in 1000 and it divides all numbers by 100, 23 becomes 0,23. Then if I Sum a list of numbers it doesn't ad up coretly (even the wrong numbers don't add up) Excel doesn't work for me - although the product was provided (bundled) by Apple - apple won't support it - and neither will Microsoft so I end up with no-person to talk to - amazing. Any help from anyone would be appreciated. Thanks On 6/5/09 6:35 AM, in article 59b75f96.-1@webcrossing.caR9absDaxw, "Clive@officeformac.com" <Cli...

Biztalk or Scribe for our data integration?
We recently started using CRM 3.0 and the time has come to decide on a data migration plan. So far I've looked at Scribe and it seems promising but my higher-ups are suggesting we take a look at BizTalk. Eventually we will need to pull (two way integration will be required with at least two legacy systems) data from several legacy systems including flat files, SQL Server and a Progress database to name a few. The possibility also exists that we'll want to accept Word, Excel and HTML documents from customers and have information they contain added to our database. Is BizTalk cap...

Data value display attributes linked to table attributes
Is there a way to cause data value points to take on attributes such as bold, italic or color set at the table from which they are derived? For example, suppose I was charting hits at several web sites as function of time, and the some characteristic of the site was being altered back and forth (say font "A" and font "B") as part of an experiment. I can easily produce a line graph x=hits y=time for the various sites, but these is no way to visualize which condition (A or B) was in effect at each data value point. I'd like to be able to display the portion of each li...

My report contains multiple copies of each record
I have three tables (parent/guardian, children, otheradults). that are all related with left joins (i.e. Include ALL records from 'Parent/Guardian' and only those records from 'Adults' where the joined fields are equal.) by ContactID (which is the parent/guardian ID) same for children. I created a query called Family which successfully combined three queries of each table where I combined the first and last names into one field. When I create my report, it creates an entry for EACH child rather than listing the children and related adults under Parent/Guardia...

moving Exchange 2003 mailboxes from multiple domains to one new do
My compnay has Exchange servers in three different domains. We have built a new domain and want to move all mailboxes into one exchange server in the new domain. We have let mailbox size get out of hand and some users have 2GB mailbox size. exporting old mailbox data to PST and importing into new exchange server is possible but would take forever (hundreds of users). any ideas how to streamline this process? or an alternate method of attack? On Wed, 28 Mar 2007 08:24:06 -0700, Kemper <Kemper@discussions.microsoft.com> wrote: >My compnay has Exchange servers in three different d...

Pivot Tables
Hi, I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% ...

Two tables, one form
I have a form that I created in AC2003. This form will merge info from two tables. So I assigned Table1 as the source for the form, inserted all of the columns from Table 1. Next, I changed to source for the form to Table2 and again inserted all of the columns from Table2 onto the form. I then changed the name of all Table2 columns to precede it with an "n". i.e. FirstName became nFirstName. All source for those items for Table2 were changed to have no source. Again, the source for the form is Table1. I lookup a row in Table1 in Table2 and if a match is found, ...

How to read values from a table?
Hi, I am trying to redesign an old database. I have three forms start, entry, edit forms. Start form is like a switch board to enter into the forms. An audit is entered into the database through "Entry" form which has around 50 controls. All these controls gets stored in the main table. "Edit" forms allows you to edit the audit. The issue here is the "Edit" form doesn't read the values from the main table when the form is loaded. I have a logic to read the values from the main table in "start" form. Everything looks fine with the SQL statemen...

Need to extrcat data from one Excel file to an other
Hi there I'm looking form a solution to press on button in a Excel sheet which ill exctract some datas from an other Excel sheet which can be close or opened. It should beworing on Windows XP, 2003, Vista and 7 as well with 32 bits and 64 bits. Thanks in avance for your advise and best regards Tra Browse through the example codes found at Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Look at the 'Copy/paste/merge" section. Should be some good material to start with. Gord Dibben MS Excel MVP On Sat, 28 May 2011 19:53:03 +0200, Tra Sumaka <trasumak...

enter data to different tables through one form
i have 5 tables. all of them has pers_no as primary key. they have one to one relation ship through the primary key amongest them. i want to create one form having a text box pers_no where i will enter the data and i want the data to get entered in each table. so how i am not able to do that. i request all you guys to help me. thank you. Sorry for the bad news, but that's not the right way to use Access. You cannot write to 5 tables from one form like that. If you are subclassing, you could create a form for entering the main form info, and then 5 subforms (probably on tab pages) f...

Scatter plot
I have an XY scatter graph with some points in it. My data in the spreadsheet looks like this: [Title] [X-value] [Y-value] [size] Data 1 | 10 | 10 | 1 Data 2 | 33 | 44 | 5 What I want to do is to change the size of the data point according to the value I specify. Right now, I have to individually modify each point and add a number corresponding to the size. Is there anyway to take the size-value from the spreadsheet and change the size of the point in the graph? In the long run, I am also looking to modify the color. If the solution requires some VBA coding, I am up for that. Any sugges...

Multiple Owners: One Territory
We have support staff that works witih the territory owner. Sharing accounts in the territory is impossible to keep up with. I'd have to manually share all accounts in the territory each month we upload new sales data. (we have at least 150K accounts) Is there any way to associate a territory to multiple owners? ...

Importing data #2
hi I would like to import data from the web and although this is quite simple in Excel 2003 the new data (that comes in upon refresh) replaces the old. I was wondering if there is a way to keep previous history of the data, make Excel fill in adjacent cells with the new data for example. Thanks for taking the time to read this George hi, if you are using Microsoft query, the answer would be no. The Micorsoft query becomes a named range in excel that is linked to the query. by desing it expands and contracts at refresh to accommidate new data. I think to accomplish what you want yo...

Offsetting Chart data
I require to view data in chart format, the problem is as the data lines are so close I need to offset the data to view all on one chart for comparison. Can anyone help me with this?? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ What does that mean? "Offset the data?" If you want to see only a subset of the data, see the Dynamic Charts page of my site. You should be able to adapt one of the examples to your needs. -- Regards, Tushar Mehta, MS MVP -...

Alternate Column Shading to be applied only to visible columns
Dear Experts: Below macro applies alternate shading to the used range considering ONLY visible rows (alternate row shading to visible rows). Could somebody please re-write the code to perform COLUMN banding (alternate column shading) instead of row banding (alternate row shading) . Only visible columns should be considered. Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub ColorRows() Dim c As Range Dim CI(0 To 1) As Long Dim i As Long Dim Rng As Range CI(0) = xlColorIndexNone CI(1) = 15 'I actually changed this from her's for li...

Pivot Table 2007 display field only in Grand Total
Dear Excel experts, I have a Pivot Table (Excel 2007). As an example, the table consists of two Colum Labels (Expense 1 and Expense 2). Values under the Column Labels are Actual Expense, Budget and Full Year budget. All Values (Actual Expense, Budget and Full Year budget) are totaled in the Grand Total. However, Management want to see Full Year Budget only in Grand Total (not under Expenses 1 and Expense 2). I was manually hiding those values. Is there a way to automate this? Thanks ...

OLE Object Data Type Question
Hi, a friend of mine supports a very simple Access 2000 database for keeping tracks of his .JPG file. The database has a table with several field, and one of them is of OLE Object data typy. He opens that table, rightclicks OLE Object field, selects Insert Oject and creates a link to a .JPG file. After finishing insertion the OLE Object field contains kind of "MSPicture3" or "MSImage3" (I don't remember exactly the text). Before a month ago he moved his database to another computer and now after insertion into the OLE Object field a link, it contains "...

Data Labels on Clustered Column w/ 3D Visual effect #2
Clustered Columns can have Alignment as "Inside End" This doesn't seem possible with 3D Visual effect Is there a way to resolve this? Yes, use 2D charts. They provide a better representation of the values being plotted without the implied false third dimension. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ emwashburn wrote: > Clustered Columns can have Alignment as "Inside End" > > This doesn't seem possible with 3D Visual effect > > Is there a way to ...

Quaters In Pivot Tables
Pivot Tables default to the first Quarter of a year commencing January. Is it possible to have the first quarter commencing July, as per Finacial rather than Calender year? Are you achieving your Quarters by Grouping a date field from your data source? If so, I don't think you can group dates in any other way--I've asked this question myself and done web searches and didn't find a different way to group. This might solve your dilemma, though (you'll have to ditch the Grouping): Create a Calculated Item that sums all dates in the appropriate range. Basic Instructions fo...

Database table relationships
In visio 2007 I can not connect the relationship heads and tails to tables - Any suggestions? Thanks ...

XML Insert data C# ASP.net 2.0 Need to know how to insert by speci
Hello, I have the following XML Schema: <?xml version="1.0" encoding="utf-8"?> <Schedule> <Day Name="Monday"> </Day> <Day Name="Tuesday"> </Day> <Day Name="Wednesday"> </Day> <Day Name="Thursday"> </Day> <Day Name="Friday"> </Day> <Day Name="Saturday"> </Day> <Day Name="Sunday"> </Day> </Schedule> I want users to be able to input the following tiomeblocks for a scheduling s...

Table Security Violation
We have a modified report called POP Purchase Order Blank Form. I'm able to print it out as sa, however, our purchasing manager is no longer able to use it. He gets the error "Table Security Violation". After trying all sorts of different combinations using Advanced Security, I finally just copied sa's security into our Purchasing Manager's account. Believe it or not, it still wouldn't work. We've reimported the report and didn't help either. I can't ease the security any more than I already have, what other options do I have? What am i missi...

Getting data from Access
I am trying to use the Get External Data function to pull data from an Access query however I keep getting an error that says: "Microsoft Query: msqry32.exe - Application error The instruction at 0x000000072 referenced memory at 0x000000072. Memory could not be read Click OK to Terminate Click on CANCEL to debug" I am using Excel and Access 2000. Does anyone have any idea why I keep getting this error? Check out this page http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "NChris" <NChris@discussions.micr...