Finalizing sheets and copying to a new book

[pip56789]

Hi All-

I'm an experienced SQL guy, trying to get up to speed on VBA. Having a hard time getting through the documentation, so bear with me!

I have a frequently-used XLS template that I manually "finalize" before sending off to my colleague. The process has five steps:

1. Remove non-numeric cells (i.e. "empty" cells that contain formulas) from a data set
2. Change the range values on a graph to match the new data
3. copy/paste values on an entire sheet to remove formulas
4. paste the sheet into a new workbook
5. apply theme formatting to the new workbook


Here is my effort at VBA scripting. It throws all sorts of errors. I've also attached the workbook for your reference. The last three tabs are the relevant ones. Help is appreciated!

Please Login or Register  to view this content.

[blane245]

Welcom to the forum!

First look, in the first two With blocks you are missing the "." in front of Range and SeriesCollection, respectively. Fix those first and see what happens next.

[pip56789]

Hey, thanks good catch. But still getting errors.
"Object doesn't support this property or method" fires on .SeriesCollection(1).XValues...

Any thoughts?

[blane245]

change .SeriesCollection to .Chart.SeriesCollection. BTW, while you are in VBA, if you put your cursor on a keyword and hit the F1 key you will find out a lot.

[pip56789]

Ah. Thank you. The F1 key is indeed useful... wasn't aware of documentation at that specificity. it's great.

I now get a third error: "The specified dimension is not valid for the current chart type" at newwb.applytheme "c:\datadump\Mytheme.thmx") the file path is right... I copied that from the macro recorder.

[blane245]

Unfortunately for you, I am not a Excel 2007 user, only 2003, and themes are not supported there. It looks to me like Excel is having trouble with the contents of the theme file, not the file itself. From the error message it looks like the theme file was created that has some information in it that doesn't apply to the workbook you are using. How did you create the theme file?

If we can't resolve this problem shortly, I suggest you start a new thred with the title of something like "ApplyTheme error" and I'll stay away from it.

[pip56789]

Hi Blane,

Yeah, I have no idea how I created the theme... it was a preset theme that I made minor changes to. I'll start a new thread. But everything else works. Thanks so much for your help. I am still learning the level specificity with which I need do define objects.

[blane245]

Always remember the F1 key. It's a great tool when your working with VBA as a new language. Also, mark this thread as [SOLVED]

[pip56789]

Hey Blaine,

I received a new error:

I left the "." off the range. When I added it, I get the object-defined error. When I remove the ".", everything appears to run OK until the end, when I get a message saying the number of data points you can use in a data series for a 2-D chart is 32000..."

I'm guessing that I haven't properly set my range. Any thoughts?

This error wasn't apparent at first because I had screen updating off.