Create Pivot Table Macro - Working In One Workbook But Not Another?

**vwhite** · 06-29-2020, 09:48 AM

Hi All

I've been puzzling over this for the last 2 days but I am truly stuck.
I have the following piece of code which creates a pivot table from the data on a new sheet

Please Login or Register  to view this content.

It works perfectly in workbook 'Create Pivot VBA Test.xlsm.
But I'm getting a run time error in 'NEW SLA v6.21 (no data).xlsm'

The code and sheet names are the same in both, so I can't figure out why I'm getting an error in one an not the other??

Any help/advice greatly appreciated
Many thanks
Vicky

**nankw83** · 06-29-2020, 10:12 AM

Hi Vicky,

The issue is in your SLA file, you have data in rows 1 & 2 so when you use A3.CurrentRegion is considers row #1 as the header row & in some columns you don't have data & thus you can't make a pivot table. To see what I mean, you can run the below code & see what is selected

Please Login or Register  to view this content.

To fix your issue, try to change your code to the below referencing the table name directly

Please Login or Register  to view this content.

**vwhite** · 06-29-2020, 10:34 AM

Fantastic. Thank you

I swear this forum keeps me in a job!

All the best
V

**nankw83** · 06-29-2020, 10:36 AM

Glad to help & thanks for the reps

Create Pivot Table Macro - Working In One Workbook But Not Another?

LinkBack

Thread Tools

Rate This Thread

Display

Create Pivot Table Macro - Working In One Workbook But Not Another?

Re: Create Pivot Table Macro - Working In One Workbook But Not Another?

Re: Create Pivot Table Macro - Working In One Workbook But Not Another?

Re: Create Pivot Table Macro - Working In One Workbook But Not Another?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Require VBA Code for create Pivot table in New Workbook

Macro to copy a pivot table filter, then to create a new workbook and paste special values

How to create a dynamic macro that will create pivot table

Create a macro to create a pivot table in a worksheet

how to create a pivot table in another workbook??

Create a Pivot Table referencing the data from another workbook (VBA)

Creating macro to create 2 worksheets from a pivot table and move them to a new workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions