Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot tables by VBA for varying amounts of data
Hi all,
I have to create a number of pivot tables from the same block of data, and repeat this with new data four weeks later etc. Data is always in the range ("A3:V" & x) where x varies each 4 weeks. I have successfully in the past substituted "selection" for the R1C1 range created in a recorded macro for 'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:=Selection)' but this is now coming up with a "type mismatch" error. If anyone has any ideas I would be very grateful. I am using Office XP. TIA Paul -- Paul at preeve dot plus dot com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot tables by VBA for varying amounts of data
Dim rng as Range
With Worksheets("Data") set rng = .Range(.Cells(3,1),.Cells(rows.count,1).End(xlup)) End with set rng = rng.Resize(,22) ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlDatabase, _ SourceData:=rng.Address(1,1,xlR1C1,True) -- Regards, Tom Ogilvy "Paul" wrote in message ... Hi all, I have to create a number of pivot tables from the same block of data, and repeat this with new data four weeks later etc. Data is always in the range ("A3:V" & x) where x varies each 4 weeks. I have successfully in the past substituted "selection" for the R1C1 range created in a recorded macro for 'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:=Selection)' but this is now coming up with a "type mismatch" error. If anyone has any ideas I would be very grateful. I am using Office XP. TIA Paul -- Paul at preeve dot plus dot com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot tables by VBA for varying amounts of data
thanks Tom, I'll try that in the office tomorrow
Paul -- Paul at preeve dot plus dot com "Tom Ogilvy" wrote in message ... Dim rng as Range With Worksheets("Data") set rng = .Range(.Cells(3,1),.Cells(rows.count,1).End(xlup)) End with set rng = rng.Resize(,22) ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlDatabase, _ SourceData:=rng.Address(1,1,xlR1C1,True) -- Regards, Tom Ogilvy "Paul" wrote in message ... Hi all, I have to create a number of pivot tables from the same block of data, and repeat this with new data four weeks later etc. Data is always in the range ("A3:V" & x) where x varies each 4 weeks. I have successfully in the past substituted "selection" for the R1C1 range created in a recorded macro for 'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:=Selection)' but this is now coming up with a "type mismatch" error. If anyone has any ideas I would be very grateful. I am using Office XP. TIA Paul -- Paul at preeve dot plus dot com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Pivot tables by VBA for varying amounts of data
....and it worked fine. Thanks again
-- Paul at preeve dot plus dot com "Paul" wrote in message ... thanks Tom, I'll try that in the office tomorrow Paul -- Paul at preeve dot plus dot com "Tom Ogilvy" wrote in message ... Dim rng as Range With Worksheets("Data") set rng = .Range(.Cells(3,1),.Cells(rows.count,1).End(xlup)) End with set rng = rng.Resize(,22) ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlDatabase, _ SourceData:=rng.Address(1,1,xlR1C1,True) -- Regards, Tom Ogilvy "Paul" wrote in message ... Hi all, I have to create a number of pivot tables from the same block of data, and repeat this with new data four weeks later etc. Data is always in the range ("A3:V" & x) where x varies each 4 weeks. I have successfully in the past substituted "selection" for the R1C1 range created in a recorded macro for 'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:=Selection)' but this is now coming up with a "type mismatch" error. If anyone has any ideas I would be very grateful. I am using Office XP. TIA Paul -- Paul at preeve dot plus dot com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a chart with varying data value ranges | Excel Discussion (Misc queries) | |||
creating a pivot table from 4 pivot tables | Excel Worksheet Functions | |||
Creating Pivot Tables | Excel Discussion (Misc queries) | |||
compare data from two tables with varying record numbers | Excel Worksheet Functions | |||
Remove varying amounts of space characters | Excel Discussion (Misc queries) |