Invalid procedure with Create pivottable

[sam452]

Thanks to help from this group, I'm on to the second stage and that is to take the data imported from "Sheet1" and generate a Pivot Chart. I did a "record macro" to get the basics of the chart and started by pasting that code into its own procedure. However, it errors trying to get to first base with a 400 error. "Invalid procedure call or argument".

Please Login or Register  to view this content.

Googling around, I can tell you it occurs when running the first time, and it has no trouble creating the empty worksheet. And of course, it's from the same version of Excel so it should have no trouble running its own code.

I've removed the parts that were optional but it still fails. None of these variables exist in this new document as it is new when the first macro runs to pull/format the data in "Sheet1".

A Windows Excel newbie as you can tell, thanx, sam

[Izandol]

Do you have Sheet4 in the workbook already? Does it have a pivot table already?

[sam452]

No, only have Sheet1, 2, 3. No pivot table/chart because I had just imported the raw data with another procedure.

Even if there was a Sheet4, it happily creates subsequent blank worksheets before it errors out, sam

[Izandol]

If Sheet4 is not present there will be an error.

Please Login or Register  to view this content.

[sam452]

Thank you, running your code above as is, adding "End Sub" and adding Sheet4 still yields a 400 error. And adds a blank Sheet5.

Rerunning it as below yields a "This Pivot Table field name is not valid" error.

Please Login or Register  to view this content.

My data on Sheet1 has column headers, the same column headers on Row1 that I used to generate the "Record Macro" that is the basis of this effort and it was happy to use these column headers. There are no blank records that I've found and the columns seem to be right.

Spending the day researching possible causes for this error, I followed this link and added apostrophes to "Sheet1" and still get the same field name not valid error.

I don't see where to set the pivot table field name? thanks for helping me get to first base, sam

[Izandol]

The field names come from the first row of data. What do you have in cells A1:K1 on Sheet1?

[sam452]

YES, thank you for pointing out my mistake. Counting my columns I find that I don't have 11 but 9 so my SourceData is wrong. I guess it's an "off by two" error. Thanks again for your time, sam