export value-copy to Mac excel

M

MikeF

Have a six-sheet dynamic file that needs to be turned into a static report
each day.
Normally no problem --- file save as whatever / select all sheets / select
all cells / copy / paste values.

But there are pc Excel 2007 tables in the file, graphs, and live query links
to Access.

When I email the file, anyone with Mac Excel loses calc cells in the tables
[they become #value#], which then knocks out the graphs/etc.

Need a sub-routine that will first convert all tables in the file to ranges,
then value-copy everything.

Can anyone assist?
Thanx in advance.

- Mike
 
B

Barb Reinhardt

You could do something like this to convert all tables to ranges on all
worksheets. It appears that the calculations are converted as well.

Sub FindTables()
Dim myListObj As Excel.ListObject

Dim WS As Excel.Worksheet

For Each WS In ThisWorkbook.Worksheets
Debug.Print WS.Name
For Each myListObj In WS.ListObjects
Debug.Print myListObj.Name
myListObj.Unlist
Next myListObj
Next WS
End Sub
 
M

MikeF

Barb,
Works great, thank you.
- Mike

Barb Reinhardt said:
You could do something like this to convert all tables to ranges on all
worksheets. It appears that the calculations are converted as well.

Sub FindTables()
Dim myListObj As Excel.ListObject

Dim WS As Excel.Worksheet

For Each WS In ThisWorkbook.Worksheets
Debug.Print WS.Name
For Each myListObj In WS.ListObjects
Debug.Print myListObj.Name
myListObj.Unlist
Next myListObj
Next WS
End Sub


MikeF said:
Have a six-sheet dynamic file that needs to be turned into a static report
each day.
Normally no problem --- file save as whatever / select all sheets / select
all cells / copy / paste values.

But there are pc Excel 2007 tables in the file, graphs, and live query links
to Access.

When I email the file, anyone with Mac Excel loses calc cells in the tables
[they become #value#], which then knocks out the graphs/etc.

Need a sub-routine that will first convert all tables in the file to ranges,
then value-copy everything.

Can anyone assist?
Thanx in advance.

- Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top