Syntax Errors when building pivot table.

Hi All,
I am trying to use lastrow method to determine the lastrow used and then build a pivottable.
However, got into syntax errors. Any help is appreciated.

Please Login or Register  to view this content.

What I am trying to do is for Row 6 and 7.

Row 6
"Sheet1!R2C2:R65000C34", Version:=xlPivotTableVersion12).CreatePivotTable _
Change to
"Sheet1!R2C2:"R"&Lastrow & C34", Version:=xlPivotTableVersion12).CreatePivotTable _

Row 7
TableDestination:="Sheet1!R30C3", TableName:="PivotTable2", _
Change to
TableDestination:="Sheet1!"R"&AddRow &C3", TableName:="PivotTable2", _

Regards,
Nironto

Hi HaHoBe,
Changing the rows as you instructed does not work as the row 5-8 is one whole.

Hi Mordred,
I have the excel book working already. But instead of hardcoding the row no. I want it to be flexible based on the no. of rows.
So if i tried using the declared variables, it will have syntax error.

You may try out on the attachment.

e.g. row 7
TableDestination:="Sheet1!R30C3", TableName:="PivotTable2", _
Change to
TableDestination:="Sheet1!"R"&AddRow &C3", TableName:="PivotTable2", _

Regards,
Nironto

Thanks MarvinP,
What I want to do is quite similar to yours.

What happens is that rows may get added/ updated from time to time. (They are refreshed via odbc).
And when so, the last row may then be changed from row no. 19 to row 50 etc.

Your pivot is on the right hand side. I want the pivot to be at the bottom. ( 3 rows down from the last row.)

(Thus Bottom instead of right). My pivot is working if on the right.
See what happens is that if is fixed at row 30 Column 3, then i will have issues when records are more than 30.

Please Login or Register  to view this content.

Regards,
Nironto

Hi All,
To better explain my point.
I had modified the excel sheet to have the pivot included.

My issue is not about the pivot table. ( which is already working).
My pain is that now the rows are e.g. 19. And the pivottable script is fixed at row 30.

If i simulate the case where i have more than 30 rows. ( Which you can just add data or simply drag down to row more than 30.), then you get data truncated issue because the position is fixed at row 30.
To test,
delete the pivot table, add in more rows then click on the macro button.

What i want is to have more control that based on last row + 3 which can be done if this syntax error is solved.

Please Login or Register  to view this content.

The above is working (but is fixed position at Row 30). But I want to change to using the variables as below.

Row 6
"Sheet1!R2C2:R65000C34", Version:=xlPivotTableVersion12).CreatePivotTable _
Change to
"Sheet1!R2C2:"R"&Lastrow & C34", Version:=xlPivotTableVersion12).CreatePivotTable _

Row 7
TableDestination:="Sheet1!R30C3", TableName:="PivotTable2", _
Change to
TableDestination:="Sheet1!"R"&AddRow &C3", TableName:="PivotTable2",

Regards,
Nironto

Hi All,
I managed to find a solution.

For the benefit of others who might have the same issue,
I use the method of declaring another variable as string, e.g. LastRow1

LastRow1 = "Checkout!R2C2:R" & LastRow & "C34", then input that as a value into the entire formula.

Regards,
Nironto

Hi,
Can you please post here full code? I need it for reference as i facing the same problem as well.