Changing PivotTable SourceData 1

[Igwiz]

Hi Guys,

Can't seem to use source data properly.

Simply put, I copy and paste an matrix of data into an area which already has a pivottable associated with it. If I just do a PT refreshtable then I might miss some new data that is now outside the range (on rows only) so I thought this might work:

For Each PT In Sheet.PivotTables
PT.SourceData = Range(Cells(18, 1), Cells(18, 1).End(xlDown).Offset(0, 4))
PT.RefreshTable
Next PT

ie Set sourcedata to a18all rows below it)

But this isn't the correct use of sourcedata.

Any quick suggestions?

Thanks.
Ig

 

[SkipVought]

Hi,

You need to used a Dynamic Range.

In Insert/Name/Define window enter a name like dbMyTable in the Names in workbook textbox and then in the Refers To textbox enter this formula, assuming that your data table begins in A1 and has no other data below the table and no other heading in row 1 to the right of your table...

=offset(Sheet1!$A$1,0,0,Counta(Sheet1!$A:$A),Counta(Sheet1!$1:$1))

Then use that name in the PivotTable Range textbox...

=dbMyTable

VOLA!


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

Sounds good - how would this change if my data starts in a18 with that row as the header?

Thanks.
Ig

 

[SkipVought]

There cannot be anything in column A rows 1-17

=offset(Sheet1!$A$18,0,0,Counta(Sheet1!$A:$A),Counta(Sheet1!$18:$18))

Also, this is assuming that your sheet is names Sheet1.

Procedurally, here's how I enter this formula...

=offset(

then click the upper-left cell of the desired range and continue...

=offset(Sheet1!$A$18,0,0,counta(

then click the left column of the desired range and continue...

=offset(Sheet1!$A$18,0,0,counta(Sheet1!$A:$A),counta(

then click the upper row of the desired range and continue...

=offset(Sheet1!$A$18,0,0,counta(Sheet1!$A:$A),counta(Sheet1!$18:$18))

What this formula does is count the rows in col A and columns in row 1 having data. -- DYNAMIC!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

Thanks!