export value-copy to Mac excel

  • Thread starter Thread starter MikeF
  • Start date Start date
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
 
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
 
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
Click to expand...
Click to expand...
 
Back
Top