Hewlp with pivot table range and table destination

[maym]

Hi

currently I have a macro that opens up workbooks in a folder, sets the area for the pivot table then places the table in cell BB1 in the current sheet.

However I get an error, "Invalid procedure call or argument" on the highlighted red section below?

Please Login or Register  to view this content.

[/COLOR]

[humdingaling]

Maybe...

xlPivotTableVersion15 = Excel 2013
your profile says excel 2010

delete the two version out of the code and see if it still gives error

Please Login or Register  to view this content.

[maym]

thanks humdingaling. I am on 2013. I will update my profile.

Managed to develop the following, however can't seem to add data fields, getting an error on the highlighted part below?

Please Login or Register  to view this content.

[/COLOR]

[humdingaling]

you can shorten your code to this

Please Login or Register  to view this content.

unsure why you would get an error on it though
if your srcdata correct?

[maym]

it was the srcdata, thanks humdingaling

[maym]

I spoke too soon

It opens up the first workbook in the folder and it works fine, but then I get an error on the second workbook

"Invalid procedure call or argument"

on this line:

Please Login or Register  to view this content.

Any ideas as to why this is happening?

[xlnitwit]

Hi,

You should really enclose the sheet names in quotes in case they include spaces or special characters, and it would be better to only use the relevant range

Please Login or Register  to view this content.

changes highlighted.

[maym]

works perfectly, thanks xlnitwit

[xlnitwit]

You're welcome.