• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Paste Pivot Table from Multiple sheets to One single Sheet

sn152

sn152

Member
Hi All,

i have a workbook where everyday we use to create pivot tables for the data in the each sheet. There are totally seven sheets. 1st three sheets have the data. Next 3 sheets are used to create pivot tables.

Note: There is only 1 pivot table in each sheet.
Sometimes pivot table may not be created for some sheets. So sheets 3,4 or 5 may not have any pivot tables sometime.

Now what I want is if there are any pivot tables in the workbook, I want them to be copy pasted in sheet 7 (One by one, that is one under another pivot table)

I have attached a sample file.

Please help me on this. Thanks in advance.
 

Attachments

  • Sample.xlsx
    29.6 KB · Views: 2
Hi Nebu,

Thanks for your reply. This is working only if I have a separate button for this macro.

But I am going to use this macro with another macro. So I am using "Call" method to call this macro. When I do so it gives an error stating "Sub or Function not defined"

Please help me.

Thanks!
 
I presume this error has nothing to do with the code . Please check whether you are not using a subroutine name which is same to that of a module name or that you have not used same name for any other subroutine as this can cause Sub or Function not defined error.
 
Hi Nebu,

Yes there is no problem with the code. There are 2 codes in the workbook that you shared. One is "pivotchk" which is written inside "ThisWorkbook" and the other is "Macro1" which is written inside "Module1".

All my codes are in Modules only. So when I use call function to call this pivotchk it shows this error. I think this is because pivotchk is in "ThisWorkbook" module.
Pls help me with this. Thanks!
 
Try copying the code in "ThisWorkbook" to a module, It is ok to put "pivotchk" subroutine in a module since it is not triggered by any workbook event. Let me know how you go...
 
Hi ,

Another option is to use the following :

Call ThisWorkbook.pivotchk

When you type in ThisWorkbook and press the period symbol . , you will see pivotchk in the dropdown.

Narayan
 
Yes When I do that the macro is working. But it is not showing any result. I forgot to tell you. I run this macro in a different workbook. That is, I have this macro in a macro file and when I run it, it opens a workbook saved in a path which has the data and it shows the result in that workbook.
 
You mean to say that you are trying to run the macro from a macro workbook to achieve the results in a different workbook, if that is the case it may not give you the desired results since in my macro I am referencing to the sheets and ranges in the macro workbook. If you want to get the results in any other workbook you will have to refer the sheet names and ranges in that particular workbook..
 
Yes I did that also. I changed the sheet names and ranges in the code even then it is not working for me. :(
 
Well I cannot fix it unless and until you upload your file it will be difficult for me to comprehend what you have done without seeing your file....
 
Hi ,

Please understand the following 2 keywords , ThisWorkbook and ActiveWorkbook.

ThisWorkbook refers to the workbook which has the code that is being executed. Thus , if your macro is in WBA , then ThisWorkbook will refer to the file WBA.xlsm or WBA.xlsb or WBA.xls

ActiveWorkbook is the workbook which is active , which may be the same as ThisWorkbook , or may be different if you have activated another workbook.

Thus , if you are running the macro in WBA , but wish all actions to be taken on another workbook , say WBB , then within your code , activate WBB , or qualify all sheet references with the ActiveWorkbook qualifier.

By default , all references are to the activesheet in the activeworkbook ; thus if you use a cell reference , such as Range("B1").Value , it will refer to the value in cell B1 in the activesheet in the activeworkbook. If you wish to refer to the value of cell B1 in ThisWorkbook , you will have to use the fully qualified reference Thisworkbook.ActiveSheet.Range("B1").Value

Narayan
 
You must log in or register to reply here.
Back
Top