RE: VBA-Pivot table creation in existing sheet one second sheet of workbook automatic

[New_Bie]

Hi All,

I need an help to create pivot table in same existing sheet. I have tried creating macros using recording and as well as on the dynamic way. However, I face runtime error at pivot creation part (Attached error images).


ivot table in existing sheet creation support required- explained below:

1)In my workbook I have multiple sheets where pivot table should always be created in second sheet - which will be the source data for pivot(sheet will be second sheet after Main data sheet) - Attached the source dump for your reference.


2)one more help required here is- name of sheet changes based on item name(Sheet name starts with space followed by name of item Eg: Item-Pencil). Also, one or two sheets will be hidden in workbook.


Hence I am struggling while creating pivot cache part. In my code, i am trying calling the sheet name with sheet index, while creating pivot sheet and facing the run-time error in below line when i step through the code.

Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheet3.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=Sheet3.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")

kindly validate and provide your help. Thanks for your support in advance!

 

[vletm]

New_Bie
If Your challenge is only that red code line then test follow:
a) Before and after sheet3.name seems to be ' - why?
b) You have given there a reference, where You have copied/modified Your code - okay?
Recheck that reference and soon You'll notice there is 'major' difference with that - You'll find it Yourself too.
After above three modifications - that code line's color will be ... black.
> I didn't try to run that code at all <

 

[p45cal]

Cross-posted

 

[vletm]

New_Bie
As a new member, You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

You seems already skipped few basic sentences from there
... and same kind of rules are everywhere.

 

[New_Bie]

Hi Vletm,

Thanks for your feedback and comments. It is cross-posted and I have resolved the issue requested in post.

I have already gone through the Forum rules. Again I have gone through the forum rules.

Could you please help me how to mark post as resolved or remove post already posted by me. SInce i could not found this in the forum rules.

 

[vletm]

New_Bie
Did You notice - I did two questions with my #2 reply? ... questions > answers

Next You wrote that You have gone through the Forum Rules ... even twice ... but
Did You read those? ... because You didn't follow those ... why?

After You know - what has solved Your thread and write it here
- You could add to end of thread's title [SOLVED] -text.