I am trying to write a macro to automate a process that happens every day.

Every day a pivot table gets some new data which is manually refreshed. This pivot table creates a new sheet by the user double clicking on the “Grand_Total and “Grand_Total” cell. I need to delete the old sheet created yesterday, create a new sheet using today’s data then rename my new sheet all inside my macro.
How do I do this?

Thanks.

Hi
do you mean that the old data is entirely discarded and only the n ew data is taken into account?
Also if a new sheet is created by clicking on a cell, I suppose a macro is running? Could you please post a sample sheet showing data and containing macro?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

There is no macro yet so I can't upload it.

There is a pivot table. When you double clicking on the “Grand_Total and “Grand_Total” cell in a pivot table a new sheet is created containing the data from the pivot table.

Part of my question is how to handle the issue of every day the pivot table creates a sheet of data it uses a different name.

Perhaps you can create a pivot table to see what I am talking about.

Hello Bob,

I would suspect if you right click the tab of the sheet containing the “Grand_Total and “Grand_Total” cell that you double click,
then click View Code,
the sheet module that opens would contain a
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) event procedure.
If so, you should post it so we have something to work with.

Sounds like the perfect opportunity to use Excels' built in macro recorder to record the daily process.
Posting this would also be of great help towards getting assistance.

So you are creating a new pivot table each day based on new daily data and the old data is erased?
You only use a Pivot Table for one day?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

NoSparks.

Using the macro recorder is what I started doing. The problem is every time I double click on the “Grand_Total and “Grand_Total” cell When it creates the new sheet it gives it a different name. The next steps in the macro need to get the data from this newly created sheet. Tough to repeat when it is always a different name!

When I click on the sheet with the pivot table in it, right click and select view code there is no code there.

Pecoflyer

The pivot table normally doesn't change, only on rare occasions. The data in it every day does change.

Can anyone help with this?

You've supplied neither a sample Excel file nor any attempt at a macro.
What type of help are you expecting ?

I can't upload it as it is a work file.

"There is a pivot table. When you double clicking on the “Grand_Total and “Grand_Total” cell in a pivot table a new sheet is created containing the data from the pivot table.

Part of my question is how to handle the issue of every day the pivot table creates a sheet of data it uses a different name.

Perhaps you can create a pivot table to see what I am talking about."

I guess it's your choice of whether or not to supply anything to work with.

I wish you luck with your project.

You can exactly create the same circumstance by creating a pivot table. When you double click on the Grand Total/Grand Total intersection cell it creates a new sheet. When you do it again, it creates a new sheet with a different name.

I want to create a macro that does all of this automatically. It is difficult when the name of the new sheet changes every time.

Post the macro that creates the new sheet.

1 There is no macro yet

2 It is a work product and I can't post it.

3 You can manually create the issue I am describing by following the steps I have outlined for you several times.

Good luck with your project.