Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
supress prompt for named ranges when copying sheet to new workbook
basically i am copying 10 charts from seperate workbooks into a ne workbook using a macro. however those 10 workbooks have the same name ranges in each, so when it copies each chart into the new workbook i prompts that the named ranges already exist and asks yes to use th source one or no to use the destination one. now none of these named ranges are relevant because i have delinked th charts from the chart data, but because theres 3 named ranges in eac file, i have to click yes 30 times. im sure there must be a way to bypass the query somehow or copy th graph without the named ranges. the code im using for copying eac graph over is Set snagworkbook = Workbooks.Open("list - BM1.xls", False, True) 'Set snagworkbook = Workbooks.Opensnagworkbook.Charts("Sna DelinkChartFromData ActiveWorkbook.Unprotect Sheets("Graph").Move ActiveSheet.Name = "BM1" Set chartworkbook = ActiveWorkbook snagworkbook.Close False it basically opens the source book as read only, moves the graph int the new workbook then closes the source unsaved. works fine except fo the prompting about the named ranges -- neowo ----------------------------------------------------------------------- neowok's Profile: http://www.excelforum.com/member.php...nfo&userid=594 View this thread: http://www.excelforum.com/showthread.php?threadid=50724 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
supress prompt for named ranges when copying sheet to new workbook
application.displayalerts = false
'do your stuff/move application.displayalerts = true neowok wrote: basically i am copying 10 charts from seperate workbooks into a new workbook using a macro. however those 10 workbooks have the same named ranges in each, so when it copies each chart into the new workbook it prompts that the named ranges already exist and asks yes to use the source one or no to use the destination one. now none of these named ranges are relevant because i have delinked the charts from the chart data, but because theres 3 named ranges in each file, i have to click yes 30 times. im sure there must be a way to bypass the query somehow or copy the graph without the named ranges. the code im using for copying each graph over is Set snagworkbook = Workbooks.Open("list - BM1.xls", False, True) 'Set snagworkbook = Workbooks.Opensnagworkbook.Charts("Snag DelinkChartFromData ActiveWorkbook.Unprotect Sheets("Graph").Move ActiveSheet.Name = "BM1" Set chartworkbook = ActiveWorkbook snagworkbook.Close False it basically opens the source book as read only, moves the graph into the new workbook then closes the source unsaved. works fine except for the prompting about the named ranges. -- neowok ------------------------------------------------------------------------ neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940 View this thread: http://www.excelforum.com/showthread...hreadid=507242 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Named Ranges | Excel Discussion (Misc queries) | |||
Copying named ranges from one sheet to another | Excel Programming | |||
named ranges and copying sheets to another workbook | Excel Programming | |||
ADO - recordset - closed excel workbook - know sheet name and cell name but no named ranges defined | Excel Programming | |||
How to supress a Save prompt? | Excel Programming |