Get data into PivotTabel

[Silaws]

I have a sheet " get data " with a table where employees need to enter data , can anyone help me with a macro that retrieves the data to a pivot table and resets the table , or possibly just retrieves the data and puts it at the bottom of a table on a new sheet "locked data " so I can make my PivotTable therefrom

 

[AlexandraT]

If you make the table in your "get data" sheet an actual table (Insert -> Table - http://www.contextures.com/xlExcelTable01.html) and then use that as the source for your pivot, you can simply refresh the pivot and any new lines will be included. This assumes that each employee would go to the last line in the table and add the data in the following line (which becomes part of the table once data is inserted anywhere in the line)

Alex

 

[Silaws]

Thanks Alex


But I have to do it the other way , it must idiot ensured and it will quickly become a very long list when around 20 people adding data several times a day

 

[AlexandraT]

It would still be fine I think. You would have your table with let's say 10 rows. The 11th row is not part of the table but once the employee starts typing anything in this 11th line beneath the table, the line itself will become part of the table. This happens automatically and for every new line just beneath the table. As long as the table is the source of the pivot, you can just refresh. It seems idiot proof to me. You can also set the pivot to refresh automatically every time the workbook is open as (Analyze -> Options -> Data -> check "Refresh data when opening the file").

Alex