Excel VBA help

[amatureleccy]

I recorded a macro of the creation of a pivot table.

The bit of code that does the business is shown below.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'ALL'!R1C2:R1655C7").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"

My problem is that the SourceData uses fixed cell addresses R1C2 and R1655C7.

I regularly add rows to the data and want rebuild the pivot table. I therefore want to have the code use the values from a variable containing the address of the last cell.

I don't know how to write the SourceData bit so that it uses the variables.

Any help would be greatly appreciated.

 

[TexMex]

I would suggest that you choose the last entry on column 1 of your source data sheet to indicate the length of your table. You could then insert this into the rest of the hard coded range. So something like the following:


'Declare variables
Dim TableLength As String
Dim FullRange As String

'This next line returns the last row number of column 1
TableLength = Worksheets("'ALL'").Rows(1).End(xlDown).Row

'This line inserts the Row found above into the full Range that you want
FullRange = "'ALL'!R1C2:R" & TableLength & "C7"

'This line is a copy of your original line BUT
'Substitutes the variable "FullRange" for your original hard coding

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=FullRange).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"


Something that worries me, however, is that you appear to have appostrophies in the name of your worksheet (ie 'ALL' ) My version of excell (2000) doesn't allow this.

 

[pipme]

Ever tried playing with 'array functions' ?
the function below effectively 'looks' down col from L4 to L1000, if the word 'pet' is found then the corresponding value in col M is added, else if not 'pet' then '0' is added, to form a sum of all 'pet' values ... I think 'pet' could be a fixed ref cell hence have a variable 'word'.

{ SUM ( IF ( (L4:L1000 ) = "pet" , ( M4:M1000 ) , 0 ) ) }

The 'curly brackets', denoting array function are inserted by <ctrl> <Enter> to complete the entry and insert into cell.

Not an answer to the problem set, but worth a thought when designing a spreadsheet.
P

 

[amatureleccy]

Cheers TexMex you've carcked it for me.

And that array stuff - why didn't I think of that - more for me play with.

Cheers to you both.

 

[pipme]

Excel is a fab program ..... Loads of M$ help and links to be googled for the huge amount of freeby stuff which exists.

Drop in here :-
http://j-walk.com/ss/excel/index.htm
Follow the 'links'.

P