Styjun

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;

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

share|improve this question

edited Mar 28 at 19:27

Rildo

asked Mar 28 at 19:22

RildoRildo

1566 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 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

 | 
show 6 more comments

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

share|improve this question

edited Mar 28 at 19:27

Rildo

asked Mar 28 at 19:22

RildoRildo

1566 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 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

 | 
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

share|improve this question

edited Mar 28 at 19:27

Rildo

asked Mar 28 at 19:22

RildoRildo

1566 bronze badges

 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

share|improve this question

edited Mar 28 at 19:27

Rildo

asked Mar 28 at 19:22

RildoRildo

1566 bronze badges

share|improve this question

edited Mar 28 at 19:27

Rildo

asked Mar 28 at 19:22

RildoRildo

1566 bronze badges

asked Mar 28 at 19:22

RildoRildo

1566 bronze badges

asked Mar 28 at 19:22

RildoRildo

1566 bronze badges

1 Answer
1

active

oldest

votes

0

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)
 ...

share|improve this answer

answered Mar 28 at 21:04

BruceWayneBruceWayne

19k1111 gold badges3434 silver badges7272 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
);



);

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

0

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)
 ...

share|improve this answer

answered Mar 28 at 21:04

BruceWayneBruceWayne

19k1111 gold badges3434 silver badges7272 bronze badges

add a comment
 | 

0

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)
 ...

share|improve this answer

answered Mar 28 at 21:04

BruceWayneBruceWayne

19k1111 gold badges3434 silver badges7272 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)
 ...

share|improve this answer

answered Mar 28 at 21:04

BruceWayneBruceWayne

19k1111 gold badges3434 silver badges7272 bronze badges

Dim rowLabel as String
...
rowLabel = Wsheet.Range("F" & i)
With .PivotFields(rowLabel)
 ...

share|improve this answer

answered Mar 28 at 21:04

BruceWayneBruceWayne

19k1111 gold badges3434 silver badges7272 bronze badges

answered Mar 28 at 21:04

BruceWayneBruceWayne

19k1111 gold badges3434 silver badges7272 bronze badges

answered Mar 28 at 21:04

BruceWayneBruceWayne

19k1111 gold badges3434 silver badges7272 bronze badges