Reference is not valid

Posted on 2012-08-23
Last Modified: 2012-08-25
Hi I am getting an error (Reference is not valid) for

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
              "mbPerFileFromPerl" & Sheets("mbPerFileFromPerl").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14). _
              CreatePivotTable TableDestination:="", TableName:="PivotTable7" _
              , DefaultVersion:=xlPivotTableVersion14

But not for this one
             ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
              "perStockTweets!" & Sheets("perStockTweets").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14). _
              CreatePivotTable TableDestination:="", TableName:="PivotTable6" _
              , DefaultVersion:=xlPivotTableVersion14

Can anyone say why that would be?
Question by:willie108

    Author Comment

    I searched on the web and it may be that dynamic named range  is necessary but I am not sure how to set that up.
    LVL 12

    Expert Comment

    It has to be an issue with the SourceData not being valid or the sheet being invalid.  Check the inner exception on the error if there is one.  it's impossible to say for sure without more info.
    LVL 35

    Accepted Solution

    It seems they are very similar with 1 exception, can you try changing

    Open in new window


    Open in new window


    Author Comment

    shoot. I missed that. THANKS!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now