How to creat a pivot-table with multiple line fields using VBA?How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?Excel VBA: clear items in pivot tablewhen changing 2 report filters on a pivot table with vba excel crashesExcel - Copying pivot tables on multiple sheets and pasting as values (retaining formatting)Excel VBA to filter Pivot Table and Pivot Chart for previous day - Pivot Filter FieldVBA Creating Pivot Table error 1004adding pivot calculated field to report using vbaVBA: Creating a Pivot TableCopy non-adjacent cells and paste transpose but not to an entire rowHow to select a pivot table data range from another sheet
Why aren't faces sharp in my f/1.8 portraits even though I'm carefully using center-point autofocus?
Do interval ratios take overtones into account or solely the fundamental frequency?
Is it ok if I haven't decided my research topic when I first meet with a potential phd advisor?
I reverse the source code, you reverse the input!
My machine, client installed VPN,
How do we know neutrons have no charge?
Are the coefficients of certain product of Rogers-Ramanujan Continued Fraction non-negative?
How do my husband and I get over our fear of having another difficult baby?
Why do some modern glider wings like the Schleicher 29 have a tadpole shape rather than a teardrop shape?
What is this end portal thingy?
A famous scholar sent me an unpublished draft of hers. Then she died. I think her work should be published. What should I do?
How much horsepower to weight is required for a 1:1 thrust ratio?
Where to find the Arxiv endorsement code?
Role of "einfach" in a certain context
Why is Pelosi so opposed to impeaching Trump?
Why would an airline put 15 passengers at once on standby?
As a team leader is it appropriate to bring in fundraiser candy?
Knights and Knaves: What does C say?
What's the hidden joke/meaning behind "Don't drink and park - accidents cause people"?
What can Thomas Cook customers who have not yet departed do now it has stopped operating?
An impressive body of work
Is the illusion created by Invoke Duplicity affected by difficult terrain?
Is there a list of world wide upcoming space events on the web?
Why is the Common Agricultural Policy unfavourable to the UK?
How to creat a pivot-table with multiple line fields using VBA?
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?Excel VBA: clear items in pivot tablewhen changing 2 report filters on a pivot table with vba excel crashesExcel - Copying pivot tables on multiple sheets and pasting as values (retaining formatting)Excel VBA to filter Pivot Table and Pivot Chart for previous day - Pivot Filter FieldVBA Creating Pivot Table error 1004adding pivot calculated field to report using vbaVBA: Creating a Pivot TableCopy non-adjacent cells and paste transpose but not to an entire rowHow to select a pivot table data range from another sheet
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I want to create a pivot table with multiple line fields. Although, these line fields need to be changed accordingly to data in typed in excel sheet.
I have tried setting the variables and using "with" and "for" commands, such as in the following code.
Option Explicit
Private Sub PivotTable()
Dim Wsheet As Worksheet, Wsheet2 As Worksheet
Dim File As Workbook
Dim PvtCache As PivotCache
Dim Pvtbl As PivotTable
Dim RLast As Double
Dim i As Variant, X As Variant
Set File = ThisWorkbook
Set Wsheet = Sheets("Data")
'Create sheet for pivot table
Set Wsheet2 = Sheets.Add(After:=Wsheet)
Wsheet2.Name = "PivotTable"
Set PvtCache = File.PivotCaches.Create(SourceType:=xlDatWsheetse, SourceData:=Wsheet.Range("A1:D45"))
Set Pvtbl = Wsheet2.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Wsheet2.Range("A3"), TableName:="Manual_Bordo")
With Pvtbl
'setting rows fields
Set Wsheet = Sheets("Data")
RLast = Wsheet.Cells(Rows.Count, "F").End(xlUp).Row 'Type the row fields in column "F".
For i = 1 To RLast
Set X = Wsheet.Range("F" & i)
With .PivotFields(X) 'Here i get the error 1004
.Orientation = xlRowField
.Position = i
End With
Next
'setting pivot Data
With .PivotFields("Size")
.Orientation = xlDataField
.Position = i
.Function = xlSum
.NumberFormat = "#.##0,0"
.Name = "Size"
End With
Next
End With
Application.DisplayAlerts = True
End Sub
But instead working I get the error 1004: cannot get the pivotfields property of the pivot table class (Sorry if it doesn't make sense, but I had to translate the dialog box).
excel vba pivot-table
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
|
show 6 more comments
I want to create a pivot table with multiple line fields. Although, these line fields need to be changed accordingly to data in typed in excel sheet.
I have tried setting the variables and using "with" and "for" commands, such as in the following code.
Option Explicit
Private Sub PivotTable()
Dim Wsheet As Worksheet, Wsheet2 As Worksheet
Dim File As Workbook
Dim PvtCache As PivotCache
Dim Pvtbl As PivotTable
Dim RLast As Double
Dim i As Variant, X As Variant
Set File = ThisWorkbook
Set Wsheet = Sheets("Data")
'Create sheet for pivot table
Set Wsheet2 = Sheets.Add(After:=Wsheet)
Wsheet2.Name = "PivotTable"
Set PvtCache = File.PivotCaches.Create(SourceType:=xlDatWsheetse, SourceData:=Wsheet.Range("A1:D45"))
Set Pvtbl = Wsheet2.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Wsheet2.Range("A3"), TableName:="Manual_Bordo")
With Pvtbl
'setting rows fields
Set Wsheet = Sheets("Data")
RLast = Wsheet.Cells(Rows.Count, "F").End(xlUp).Row 'Type the row fields in column "F".
For i = 1 To RLast
Set X = Wsheet.Range("F" & i)
With .PivotFields(X) 'Here i get the error 1004
.Orientation = xlRowField
.Position = i
End With
Next
'setting pivot Data
With .PivotFields("Size")
.Orientation = xlDataField
.Position = i
.Function = xlSum
.NumberFormat = "#.##0,0"
.Name = "Size"
End With
Next
End With
Application.DisplayAlerts = True
End Sub
But instead working I get the error 1004: cannot get the pivotfields property of the pivot table class (Sorry if it doesn't make sense, but I had to translate the dialog box).
excel vba pivot-table
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
2
Pivot tables via VBA can be difficult. Suggest try recording a macro while setting it up manually, then cleaning up the recorded macro.
– Ron Rosenfeld
Mar 28 at 19:26
Do you expectXto be a number? Like ` With .PivotFields(10)? If so, changeX` fromVarianttoLongand just doX = Wsheet.Range("F" & i).Value...if that's what you mean?
– BruceWayne
Mar 28 at 19:30
@BruceWayne I expect that X would be a row label as in.PivotFields("DATA"). It would be easier to create my pivot tables. Even though, I'll try your suggestion. Thank you.
– Rildo
Mar 28 at 19:51
@Rildo - Oh! First, I'd renameXso it's more clear -- but try this insteadDim rowLabel as String // ... // rowLabel = Wsheet.Range("F" & i)
– BruceWayne
Mar 28 at 19:57
@RonRosenfeld thank you for the idea. I use a much bigger database with over 30 columns, though. Therefore, I need to create different pivot tables just by selecting the row field instead of hard code.
– Rildo
Mar 28 at 20:06
|
show 6 more comments
I want to create a pivot table with multiple line fields. Although, these line fields need to be changed accordingly to data in typed in excel sheet.
I have tried setting the variables and using "with" and "for" commands, such as in the following code.
Option Explicit
Private Sub PivotTable()
Dim Wsheet As Worksheet, Wsheet2 As Worksheet
Dim File As Workbook
Dim PvtCache As PivotCache
Dim Pvtbl As PivotTable
Dim RLast As Double
Dim i As Variant, X As Variant
Set File = ThisWorkbook
Set Wsheet = Sheets("Data")
'Create sheet for pivot table
Set Wsheet2 = Sheets.Add(After:=Wsheet)
Wsheet2.Name = "PivotTable"
Set PvtCache = File.PivotCaches.Create(SourceType:=xlDatWsheetse, SourceData:=Wsheet.Range("A1:D45"))
Set Pvtbl = Wsheet2.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Wsheet2.Range("A3"), TableName:="Manual_Bordo")
With Pvtbl
'setting rows fields
Set Wsheet = Sheets("Data")
RLast = Wsheet.Cells(Rows.Count, "F").End(xlUp).Row 'Type the row fields in column "F".
For i = 1 To RLast
Set X = Wsheet.Range("F" & i)
With .PivotFields(X) 'Here i get the error 1004
.Orientation = xlRowField
.Position = i
End With
Next
'setting pivot Data
With .PivotFields("Size")
.Orientation = xlDataField
.Position = i
.Function = xlSum
.NumberFormat = "#.##0,0"
.Name = "Size"
End With
Next
End With
Application.DisplayAlerts = True
End Sub
But instead working I get the error 1004: cannot get the pivotfields property of the pivot table class (Sorry if it doesn't make sense, but I had to translate the dialog box).
excel vba pivot-table
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
I want to create a pivot table with multiple line fields. Although, these line fields need to be changed accordingly to data in typed in excel sheet.
I have tried setting the variables and using "with" and "for" commands, such as in the following code.
Option Explicit
Private Sub PivotTable()
Dim Wsheet As Worksheet, Wsheet2 As Worksheet
Dim File As Workbook
Dim PvtCache As PivotCache
Dim Pvtbl As PivotTable
Dim RLast As Double
Dim i As Variant, X As Variant
Set File = ThisWorkbook
Set Wsheet = Sheets("Data")
'Create sheet for pivot table
Set Wsheet2 = Sheets.Add(After:=Wsheet)
Wsheet2.Name = "PivotTable"
Set PvtCache = File.PivotCaches.Create(SourceType:=xlDatWsheetse, SourceData:=Wsheet.Range("A1:D45"))
Set Pvtbl = Wsheet2.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Wsheet2.Range("A3"), TableName:="Manual_Bordo")
With Pvtbl
'setting rows fields
Set Wsheet = Sheets("Data")
RLast = Wsheet.Cells(Rows.Count, "F").End(xlUp).Row 'Type the row fields in column "F".
For i = 1 To RLast
Set X = Wsheet.Range("F" & i)
With .PivotFields(X) 'Here i get the error 1004
.Orientation = xlRowField
.Position = i
End With
Next
'setting pivot Data
With .PivotFields("Size")
.Orientation = xlDataField
.Position = i
.Function = xlSum
.NumberFormat = "#.##0,0"
.Name = "Size"
End With
Next
End With
Application.DisplayAlerts = True
End Sub
But instead working I get the error 1004: cannot get the pivotfields property of the pivot table class (Sorry if it doesn't make sense, but I had to translate the dialog box).
excel vba pivot-table
excel vba pivot-table
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
edited Mar 28 at 19:27
Rildo
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
asked Mar 28 at 19:22
RildoRildo
156 bronze badges
156 bronze badges
2
Pivot tables via VBA can be difficult. Suggest try recording a macro while setting it up manually, then cleaning up the recorded macro.
– Ron Rosenfeld
Mar 28 at 19:26
Do you expectXto be a number? Like ` With .PivotFields(10)? If so, changeX` fromVarianttoLongand just doX = Wsheet.Range("F" & i).Value...if that's what you mean?
– BruceWayne
Mar 28 at 19:30
@BruceWayne I expect that X would be a row label as in.PivotFields("DATA"). It would be easier to create my pivot tables. Even though, I'll try your suggestion. Thank you.
– Rildo
Mar 28 at 19:51
@Rildo - Oh! First, I'd renameXso it's more clear -- but try this insteadDim rowLabel as String // ... // rowLabel = Wsheet.Range("F" & i)
– BruceWayne
Mar 28 at 19:57
@RonRosenfeld thank you for the idea. I use a much bigger database with over 30 columns, though. Therefore, I need to create different pivot tables just by selecting the row field instead of hard code.
– Rildo
Mar 28 at 20:06
|
show 6 more comments
2
Pivot tables via VBA can be difficult. Suggest try recording a macro while setting it up manually, then cleaning up the recorded macro.
– Ron Rosenfeld
Mar 28 at 19:26
Do you expectXto be a number? Like ` With .PivotFields(10)? If so, changeX` fromVarianttoLongand just doX = Wsheet.Range("F" & i).Value...if that's what you mean?
– BruceWayne
Mar 28 at 19:30
@BruceWayne I expect that X would be a row label as in.PivotFields("DATA"). It would be easier to create my pivot tables. Even though, I'll try your suggestion. Thank you.
– Rildo
Mar 28 at 19:51
@Rildo - Oh! First, I'd renameXso it's more clear -- but try this insteadDim rowLabel as String // ... // rowLabel = Wsheet.Range("F" & i)
– BruceWayne
Mar 28 at 19:57
@RonRosenfeld thank you for the idea. I use a much bigger database with over 30 columns, though. Therefore, I need to create different pivot tables just by selecting the row field instead of hard code.
– Rildo
Mar 28 at 20:06
2
2
Pivot tables via VBA can be difficult. Suggest try recording a macro while setting it up manually, then cleaning up the recorded macro.
– Ron Rosenfeld
Mar 28 at 19:26
Pivot tables via VBA can be difficult. Suggest try recording a macro while setting it up manually, then cleaning up the recorded macro.
– Ron Rosenfeld
Mar 28 at 19:26
Do you expect
X to be a number? Like ` With .PivotFields(10)? If so, change X` from Variant to Long and just do X = Wsheet.Range("F" & i).Value...if that's what you mean?– BruceWayne
Mar 28 at 19:30
Do you expect
X to be a number? Like ` With .PivotFields(10)? If so, change X` from Variant to Long and just do X = Wsheet.Range("F" & i).Value...if that's what you mean?– BruceWayne
Mar 28 at 19:30
@BruceWayne I expect that X would be a row label as in
.PivotFields("DATA"). It would be easier to create my pivot tables. Even though, I'll try your suggestion. Thank you.– Rildo
Mar 28 at 19:51
@BruceWayne I expect that X would be a row label as in
.PivotFields("DATA"). It would be easier to create my pivot tables. Even though, I'll try your suggestion. Thank you.– Rildo
Mar 28 at 19:51
@Rildo - Oh! First, I'd rename
X so it's more clear -- but try this instead Dim rowLabel as String // ... // rowLabel = Wsheet.Range("F" & i)– BruceWayne
Mar 28 at 19:57
@Rildo - Oh! First, I'd rename
X so it's more clear -- but try this instead Dim rowLabel as String // ... // rowLabel = Wsheet.Range("F" & i)– BruceWayne
Mar 28 at 19:57
@RonRosenfeld thank you for the idea. I use a much bigger database with over 30 columns, though. Therefore, I need to create different pivot tables just by selecting the row field instead of hard code.
– Rildo
Mar 28 at 20:06
@RonRosenfeld thank you for the idea. I use a much bigger database with over 30 columns, though. Therefore, I need to create different pivot tables just by selecting the row field instead of hard code.
– Rildo
Mar 28 at 20:06
|
show 6 more comments
1 Answer
1
active
oldest
votes
You're using an object where you just need a string. (I'm also renaming X to make it more clear):
Dim rowLabel as String
...
rowLabel = Wsheet.Range("F" & i)
With .PivotFields(rowLabel)
...
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
add a comment
|
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55405410%2fhow-to-creat-a-pivot-table-with-multiple-line-fields-using-vba%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You're using an object where you just need a string. (I'm also renaming X to make it more clear):
Dim rowLabel as String
...
rowLabel = Wsheet.Range("F" & i)
With .PivotFields(rowLabel)
...
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
add a comment
|
You're using an object where you just need a string. (I'm also renaming X to make it more clear):
Dim rowLabel as String
...
rowLabel = Wsheet.Range("F" & i)
With .PivotFields(rowLabel)
...
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
add a comment
|
You're using an object where you just need a string. (I'm also renaming X to make it more clear):
Dim rowLabel as String
...
rowLabel = Wsheet.Range("F" & i)
With .PivotFields(rowLabel)
...
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
You're using an object where you just need a string. (I'm also renaming X to make it more clear):
Dim rowLabel as String
...
rowLabel = Wsheet.Range("F" & i)
With .PivotFields(rowLabel)
...
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
answered Mar 28 at 21:04
BruceWayneBruceWayne
19k11 gold badges34 silver badges72 bronze badges
19k11 gold badges34 silver badges72 bronze badges
add a comment
|
add a comment
|
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55405410%2fhow-to-creat-a-pivot-table-with-multiple-line-fields-using-vba%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Pivot tables via VBA can be difficult. Suggest try recording a macro while setting it up manually, then cleaning up the recorded macro.
– Ron Rosenfeld
Mar 28 at 19:26
Do you expect
Xto be a number? Like ` With .PivotFields(10)? If so, changeX` fromVarianttoLongand just doX = Wsheet.Range("F" & i).Value...if that's what you mean?– BruceWayne
Mar 28 at 19:30
@BruceWayne I expect that X would be a row label as in
.PivotFields("DATA"). It would be easier to create my pivot tables. Even though, I'll try your suggestion. Thank you.– Rildo
Mar 28 at 19:51
@Rildo - Oh! First, I'd rename
Xso it's more clear -- but try this insteadDim rowLabel as String // ... // rowLabel = Wsheet.Range("F" & i)– BruceWayne
Mar 28 at 19:57
@RonRosenfeld thank you for the idea. I use a much bigger database with over 30 columns, though. Therefore, I need to create different pivot tables just by selecting the row field instead of hard code.
– Rildo
Mar 28 at 20:06