Replies

Steve,

Your code worked beautifully. It did exactly as I requested. One of the powerpoints (in the middle of the pack) had to be left out because all after that one had truncated data (that file must of had internal problems).
No idea why, but easy to find the trouble maker.
I showed the result to the user and they were very very happy. Of course then they changed their story (“actually I wanted the datasheet AND the graph”). I did laugh and indicate that they could take it from here.

Many many thanks!

Chuck

Steve,

I look forward to trying it! Thanks so much for taking the time.
I do not have the time to do it manually for them and was nervous with the abilities of the person they were going to assign.
I will give your code a shot.

Thanks again,
Chuck

Hans,

I agree.
But since they asked, I figured I would ask…
(thanks for all the work you save people)

Have a GREAT day!
Chuck

Here is the PowerPoint sample.

For some reason I am not able to send the ppt…. I will try a third time.
I get error message: Explanation: The request timed out before the page could be retrieved

Edited by Reimer -I figured out a way to do it. No need to waste time on reply, but THANKS

The macro runs GREAT and does exactly what the user wanted (then).
Now the user would like the macro to also save the data sheet that is in the same workbook as the charts, to the same directory the new charts are saved to. The naming convention can be the same as it is for the charts. There may be other sheets (blank or other uses) in the workbook. Since the tab name of the data sheet may change each time, I cannot test for Sheet name to determine which is the data sheet. I will just instruct the user to make sure the sheet active is the data sheet. But, I would like to change the code to determine if the active sheet is a chart or sheet and if it is a chart – warn the user to rerun the macro after making the data sheet active.
What is the VBA code to test the active sheet to see if it is a chart?

Thanks

Hans,

Thanks!
It seemed to work fine but I was concerned about future issues. For next years budget, I will rebuild it just to be safe.

Have a Great Day!

Chuck

Brad,

Not sure why it hung. Did you change the macro to indicate the correct range?
Actually, Since Hans has replied -I would just listen to him…. (smile)

chuck

Brad,

Is a macro solution ok? I have one that I got from Hans:

Sub SelectDupes_hans()
‘ Source: Woody’s Lounge Post #485599 by Hans
‘ To get the unique entries: change > 1 to = 1, the code will select the
‘ cells containing unique values instead.

Dim oRange As Range
Dim oCell As Range
Dim oSelect As Range
Set oRange = Range(“A27”).CurrentRegion
For Each oCell In oRange.Cells
If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then
If oSelect Is Nothing Then
Set oSelect = oCell
Else
Set oSelect = Union(oSelect, oCell)
End If
End If
Next oCell
oSelect.Select
Set oSelect = Nothing
Set oCell = Nothing
Set oRange = Nothing
End Sub

It selects all cells that are duplicates.
Hope this helps.
Chuck

Hans,

THANK YOU!
(actually I tried indirect first but could ot get it to work and the help did not help me)

Thank you for taking the time to help me out AGAIN!
(I just talked to the user -they are joyous)

Chuck

Colin,

Open the workbook.
From the menu Select Window | New Window
Then from the Window menu Select Compare Side by side.
If you only see one workbook, make sure it is not set to Maximum size.
This works on Excel 2003 and I think it works on Excel 2000 as well.

Chuck

samareanna,

I checked Help on the GETPIVOTDATA and found this:
Returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report.

Note You can quickly enter a simple GETPIVOTDATA formula by typing = in the cell you want to return the value to and then clicking the cell in the PivotTable report that contains the data you want to return.

I tried it in your workbook and it seemed to work fine. You can also then look at the structure of the formula to see where yours is different.

Chuck

Jerry,

Thanks.
Good to have (prints nice too).
Have a great weekend.

chuck

Jerry,

The link fails for me too.

Hans,

We made the changes to the setting you mentioned. We still got errors.
It turns out the Excel file, that was embedded, was corrupt. When we deleted that and opened the Word doc, we did not get the error message.
So we created a new Excel table and inserted into the Word doc. Now it works whether we change the settings you mentioned or not.
Thank you for all the help you so freely give!
I love this site.

Chuck

Hans,

Thanks. We will give it a try.

Chuck