Solved: Copy Values and Formats to a new workbook VBA

[gcam032]

Hi everyone,

There have been related questions on the web but nothing has really helped me unfortunately, probably because I am a complete beginner to VBA.

I was hoping to get some help on this one from someone who can work step by step.

Here's my dilemma. I have a workbook in excel 2007 full of data and sheets that all update dynamically. The person who I share this information with has excel 2003 and so some of the ways I have created the dynamic components are not compatible (especially some advanced conditional formatting).

So what I would like to do is to have a button in my 2007 workbook that runs a macro that does the following:

1. Creates a new workbook
2. Copies some specific worksheets from my 2007 workbook to the new one
- I need it to copy ONLY the values and formats and any logos that are in the sheets.

Can anyone help me get started?

Thanks in advance!!

Gareth

[Bob Phillips]

Just save the file as a 97-2003 Excel file, and all of the 2007 formats will be lost.

[gcam032]

Hi thanks for that, although it doesn't solve my issue.

If I do that, yes I can save it, but my conditional formatting that only works in 2007 is lost and some of the values that are in Euros are formatted as USD (for example). My workaround for this is to try and create a macro so that I can set my currency as Euros, run the macro to export the whole workbook as values and formats and then change the currency to USD and run it again so that I have two new workbooks with the correct formatting. Further to this I will only need certain sheets and I want to lose all formulas so that they just get a static report of values.

Thanks

Gareth

[Bob Phillips]

I think you are trying to create yourself a maintenance nightmare. I would ditch the 2007 specific CF (It is rubbish IMO anyway) and provide a workbook that is generic, works the same in 2003 as in 2007, and maybe gives the values in all currencies.

[gcam032]

Hi mate,

Yea you are probably right. I'm using this as a VBA learning exercise also though. What if I wrote the conditional formatting in VBA instead of using the menu - would this then make it function in 2003 also?

Thanks

[Bob Phillips]

VBA isn't the driver, it is only using functionality that works in both versions. Whether you do that in Excel or in VBA, the principle is the same.

The safest bet is to always develop in the lowest version that you will deploy to, you can be sure then (well, reasonably sure), that it works in both versions.

[gcam032]

Ok, thanks for your advice. Much appreciated!