VBA Macro "yes to all" on dialogue box

Question

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?

doug_robbins_word_mvp · Answer

LeeCornellWhittens&nbsp;
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
            .Names(i).Delete
        End If
    Next i
End With

leecornellwhittens · Answer

Doug_Robbins_Word_MVP&nbsp;, 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.

doug_robbins_word_mvp · Answer

Just close the files without saving them.

Forum Discussion

VBA Macro "yes to all" on dialogue box

3 Replies

Resources