How to avoid name conflicts while copying sheets

[jaffar]

hi,

Hi
i'm writing macros for an excel file.

i've a macro which will read the data from there are 30 to 40 work books
each work book contains 8 worksheets and copies that data
and paste the copied data into new workbook with corresponding 8 sheets.

it is copying the data into new work sheet. whats my problem is while coping
the data from all work books data it gives an dailog box.it gives an error
it shows the message as. i used same name in all 30-40 workbooks
_DATA_impact_image

"A formula or sheet you want to move or copy contains the name
'_DATA_impact_image', which already exists on the destination worksheet.Do
you want to use this version of the name?

..TO use name as defined in the destination sheet, click yes.
..To rename the range referred in the formula or worksheet,click no, and
enter a new name in the name Conflict dialog box."


How can i avoid this while gathering the data from the workbooks.

can anyone tell me how to do this.
its very urgent for me

thanx in advance
jaffar

 

[Bernie Deitrick]

jaffar,

Try using

Application.DisplayAlerts = False

at the top of your code, and

Application.DisplayAlerts = True

at the bottom. Excel should then default to using the local name with the formula.

HTH,
Bernie
MS Excel MVP