Jan 13 2022 04:58 PM
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?
Jan 14 2022 12:37 AM
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
Jan 14 2022 12:39 AM
@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.
Jan 14 2022 02:54 AM