Dynamic Range with another workbook

  • Thread starter Thread starter Carim
  • Start date Start date
Put the names in source workbook. When creating the PT specify the
name rather than the range.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Hello Tushar,

If I am not mistaken it is exactly what I am doing and I keep getting
an error message :
Reference is not valid ...
I guess I have missed something ...
Thanks for your help
Regards
Carim
 
In the source workbook, Book4 in my test, I created a name covering 2
columns and as many rows of data as are present:

myRng =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),2)

In Book5, the workbook containing the PT, in the PT wizard I specified
Book4!myrng as the source.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
I think I did not expressed myself clearly enough.
It works fine as long as the second workbook is open ... as soon as it
is closed, the source range is no longer recognized.
Whereas, when one uses static ranges, it works in both cases (second
workbook open or closed)
Hope I have clarified my question.

Regards
Carim
 
Think you need to include code in the source workbook which will hard code
the extent of the named range upon closing. If it is essential that it be
dynamic when opened, then in the workbook open event, redefine it with the
appropriate formula - otherwise, just depend on the beforeclose event to
hard code it.

worksheets("Data").Range("A1").CurrentRegion.Name = "Database"

as an example.
 
Tom,

Thanks for your hint ... I have now managed to hardcode the range in
the source workbook, which means I can now operate the "pivot-tables"
workbook ...
Again thanks a lot !!!
Best Regards
Carim
 
Back
Top