Solved

Vba to Fix Pivot overlapping

Posted on 2013-05-15
2
507 Views
Last Modified: 2013-05-15
Morning everyone,

I have a macro that loops through a directory and pulls back data from sql to update all the data tabs.  Then I refresh all the pivots in the workbook.  The issue I am having is sometimes the pivots will overlap and I will get an error message of "Do you want to replace the contents of the destination cells".   Since I am looping through 50 workbooks and at different time this can occur I was hoping that there was some way to programatically deal with the issue.  

Thanks,
Montrof
0
Comment
Question by:montrof
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39168094
It's really more a spreadsheet design question more so than a VBA question.

Basically, never place PTs such that upon refresh the PT can expand to destroy other content.

I follow a general rule of always:
Putting PTs on their own worksheets, away from the source data
Not putting multiple PTs on a single worksheet
0
 
LVL 1

Author Comment

by:montrof
ID: 39168297
Well it was not my design I am just automating the loading of the data to the spreadsheets.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

747 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

12 Experts available now in Live!

Get 1:1 Help Now