VBA Macro "yes to all" on dialogue box

New Contributor

Hi all, first time poster here - I use Office 365


I create reports for data each day and need to collect the same worksheet from each daily report to place in an end of month workbook.  I also need to retain the worksheet-level named ranges and the workbook-level named ranges I don't care about. So far so easy.


When it comes to the workbook level ranges though, my macro stops while a dialogue box is thrown up to advise me that the new worksheet has ranges with the same name as my destination file and do I want to use the existing ones or whatever:  Choices given are "Yes" (default choice selected by Excel), "Yes to All" and "No".


I want to code in an automatic acceptance of "Yes to All" rather than have to choose it 31 times or choose "Yes" a much larger amount of times.  I can't find a solution for this anywhere. Turning off alerts does not stop the dialog box popping up.


Here's a snip of the code after which the dialogue box pops up:

ThisDataType = "Detailed Resource Report"
Sheets(ThisDataType).Copy After:=Workbooks(StartFile).Sheets(SummarySheets)


Can anyone help?

3 Replies


If you run the following code on each workbook before it is copied, it will delete the workbook-level names

With ActiveWorkbook
    For i = .Names.Count To 1 Step -1
        If InStr(.Names(i).Name, "!") = 0 Then
        End If
    Next i
End With

@Doug_Robbins_Word_MVP , thanks for that, but I don't want to delete them. I guess I could in a pinch, seeing the files close without saving, but I really just want to accept the "yes to all" option.

Just close the files without saving them.