export value-copy to Mac excel

[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

 

[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

 

[MikeF]

Barb,
Works great, thank you.
- Mike

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

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