gerys.blogg.se

The Sales ID is the relational field that connects the tables. At the end of each year, you need a report that provides the total yearly sales by person, but you also need a report that provides the total sales by city.įor this tutorial, we’ll create a master table with the salespersons’ information and a second table that provides their total sales, by quarter, for the year. Each sales person has 200 products that he/she sells (detail or child table). For example, you have 10 sales people who all have unique, demographic information (master table). To avoid repeating all the master information in every detail table, you create relationships using one unique field, such as the Sales ID, then let Excel do the rest. The data in the detail tables-such as daily sales, product prices, quantities-usually changes constantly. This is called a one-to-many relationship. "Results!R2C1:R" & MyRow & "C" & M圜ol, _ Version:=xlPivotTableVersion15.CreatePivotTable _Īny suggestions on what is wrong with the modified code would be greatly appreciated.For every record in the master table, there can be many records in the detail tables (also called slave or child tables) that link back to the master table. Modified Code using variable to determine the last row and column: (SourceType:=xlDatabase, SourceData:= _

TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _ "Results!R2C1:R633C25", Version:=xlPivotTableVersion15).CreatePivotTable _ Recorded Code: (SourceType:=xlDatabase, SourceData:= _ I tried to modify the code to create the pivot table but I'm getting a syntax error. The one twist is that the range of data can change from user to user or time to time depending on a query they run. It has worked great for year but not users are moving to Excel 2013 and it is not working.

I have a macro that creates a pivot table.