1

I have the following code:

Dim newWorksheet As Worksheet
Set newWorksheet = ThisWorkbook.Sheets.add()
Set pivotCache = ThisWorkbook.PivotCaches.create(SourceType:=xlExternal, SourceData:=cnSet)
pivotCache.CreatePivotTable newWorksheet.Range("A1"), newWorksheet.Name

Works great 99% of the time, however today a customer sent me a version of the file that was not working, when I open the file and attempt to compile the code I get the following issue with the last line of code:

Method or Data Member Not Found

Method or Data Member Not Found

In fact you can even see that the property is missing in the autocomplete:

AutoComplete

It appears as though it's recognizing the "Worksheet" object as a "chart":

Chart Type

Strange, so I covert the "newWorksheet" over to a variant, compile, works great. Now I change it back, compile, and... it works, issue is gone, code runs great. Very strange, you can even see it in the autocomplete:

Worksheet Type

(Now it's object type "Worksheet")

I don't understand what would cause this or how to prevent it in the future, the issue only occurs with this one file. The user was not doing anything exceptional with it as far as I can tell. This issue is documented but typically is related to other problems. I don't see any explicit reference to this occurring on a per file basis.

Does anyone have any insight on this one, this is one of those "only in VBA" type of issue?

David Rogers
  • 2,601
  • 4
  • 39
  • 84
  • 1
    I can't say I've ever heard of that, but given that you want a worksheet, you might specify that to be safe: `Set newWorksheet = ThisWorkbook.Sheets.add(Type:=xlworksheet)` – Rory Apr 09 '18 at 15:04
  • @Rory, Hmm good suggestion but that doesn't seem to have any effect :( – David Rogers Apr 09 '18 at 16:04
  • If the issue can be reproduced, can you post a workbook somewhere for me to have a look at? – Rory Apr 09 '18 at 16:06
  • @Rory I would love to, but the only document I have with this issue contains proprietary code and client information, and I can't seem to get this to reoccur in any other file in a predictable manner... – David Rogers May 09 '18 at 15:57

0 Answers0