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

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