export value-copy to Mac excel
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 |
export value-copy to Mac excel
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" wrote: 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 |
export value-copy to Mac excel
Barb,
Works great, thank you. - Mike "Barb Reinhardt" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 08:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com