How to add data automatically from one sheet to another

[Regal383]

Hi guys,

Hope all is well.

I have a question.

I have a pivot table which summarizes the number of projects and it shows how many people that are involved in each project and how many hours they each put into the project. This can be found in tab "Pivot Table". Now, if you look at tab "Översikt" I want to reference the "project names", that will show up automatically in the tab "översikt" everytime the pivot table gets updated with new information. How the Pivot table gets updated automatically, is through the tab "Data". I have a form at the top where you can fill out employee name, project name, hours put and then you click at the top to add the information.

Please I need help

Is there a soul here who can help me :D

Best regards,

Sia!

[avk]

Hello Regal, Refer your attach file. As i knowing i have workout in your file in sheet "Oversikt".

In column "A" using formula "INDEX, MATCH, COUNTIF". If you add in data new project name, the same new project name add into Pivot & also in "Oversikt".

In rest of sheet "1" to 42" create "sheetname" & in some of sheet first twelve in "A5" cell i modified with "DataValidation" project name.

In those all sheets data pull out as per project name in "Oversikt". Tested in only column "C" which is highlighted.

If you add record in "Data" sheet & go to "Oversikt" sheet, data shown as #VALUE!, but if you click on "Pivot Table" & again go to "Oversikt" sheet the data show as per formula.

I have attach file. Kindly look into respective sheet.

[Regal383]

Dear AVK,

Thank you so much for your time and help, I really appreciate it. You are the best :D

But one question, when I open the excel paper, and "enable" to edit, the formulas in "översikt" turns to "#REF!", why does it do that? How to do I stop it from turning to "#REF!"?

Thanks in advance AVK.

Best regards,

SIA

[avk]

I think this can be done because of micro code, but not confirm. If anyone master in vba plz confirm.

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh  

[Regal383]

Dear AVK,

Thanks for your quick reply.

How do I apply that code and where?

Thank you in advance.

Best regards,

SIA

[Regal383]

Good morning dear AVK

In tab "översikt" on "projekt 12", it shows 111 timmar? I think this number is wrong. .

Also, it still hows "#REF!" in tab "översikt" as soon as I open the document and Excel asks me if I want to "Enable Content".

Would really appreciate if you could help me out my dear friend!

Best regards,

SIA

[avk]

Dear Friend, timmar "111" : i enter for testing purpose only. Kindly remove from "12" number sheet.
Regarding point no. 2 : Its because of micro code, (in my earlier post i mentioned that)
From "13" onword sheet kindly select in "A5" (each sheet) project name respectively.

[Regal383]

Dear avk

Thank you for your reply. Now I understand better.

But, how Do I fix the microcode, where in the VBA should I instert this code: ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh ?

You are the best avk,

Have a wonderful day my friend.

Best regards,

SIA

[avk]

>Right click on sheet tab
>click on "view code"
>go to "module1" (double click) or any listed sheet name

[Regal383]

Dear AVK,

I did as you told me to. I went to sheet tab "Översikt", right click, "view code", then I went to module 1 and pasted the code. After I pasted the code, it still says #REF! in the sheet tab "översikt". Please look at attached screen shot.
Your Messagev

[avk]

I am not familier with vba micro. Any one plz assist.

[Regal383]

Hi Avk,

Now I am thinking, is there a way around VBA? For instance, take a look at my attachment.

I have a pivot table, sheet name "pivot table", and then I have a sheet name, "data", where I fill out the persons name, project name and then at the top "Klicka här för att lägga..." is just a macro command. As you fill out the information and then click on the box, the information will appear below in the table, and the pivot table. In addition, I have added information at the sheet name, "pivot table" to the right of it, you can see information of every week of the month from januari week 1 2017 to 2018. The numbers are stating total number of hours which are references and summurization of hours from each week, this derives from sheet names "1", "2", and "3" and so forth.

My question is, when I fill out information, in the sheet name "data", how can i make automatically that I will show up in "sheet" pivot table, and similiar references i made, but instead show up automatically. I tried with "show report filter pages" which will show every project in its own sheet, but how do I manage to make it show up like that automatically when I fill out information in the sheet name "data"?
Best regards

SIA

[Richard Buttrey]

This is the same one that you started here....http://www.excelforum.com/showthread...t=#post4561174

If you have supplementary questions then you should stick to the same thread since those who helped you in the previous one will see the request and be in a better position to help you. In addition note the answer I gave you in that thread.

You should seriously consider keeping all the data you currently hold in individual tabs in the single sheet database I described.
Put together a sample test single sheet database that has an additiona column to hold whatever the current tab names are, upload it and then ask the supplementary questions.

This thread will be closed. Now post in your original above.