Disable "Select Sheet"

[M1234]

Hi,

I have a find and replace VBA formula which, when it cannot locate the specified sheet for that week, prompts the Select Sheet to popup. Is there a way for me to disable the Select Sheet popup?

Code is below:

Sub FindReplace()
Range("Warehouse_1,ST_1,STP_1,Channel_1,Year_1").Select
    Selection.Replace What:=Range("Old_Week"), Replacement:=Range("New_Week"), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Calculate
End Sub

[ragulduy]

you can set displayalerts to false, I think that would work:

Sub FindReplace()
application.displayalerts = false
Range("Warehouse_1,ST_1,STP_1,Channel_1,Year_1").Replace What:=Range("Old_Week"), Replacement:=Range("New_Week"), LookAt:=xlPart, _ SearchOrder :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Calculate
application.displayalerts = true
End Sub

[M1234]

you can set displayalerts to false, I think that would work:

Sub FindReplace()
application.displayalerts = false
Range("Warehouse_1,ST_1,STP_1,Channel_1,Year_1").Replace What:=Range("Old_Week"), Replacement:=Range("New_Week"), LookAt:=xlPart, _ SearchOrder :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Calculate
application.displayalerts = true
End Sub

That didn't seem to work

[ragulduy]

I think I forgot to remove the underscore.

Sub FindReplace()
application.displayalerts = false
Range("Warehouse_1,ST_1,STP_1,Channel_1,Year_1").Replace What:=Range("Old_Week"), Replacement:=Range("New_Week"), LookAt:=xlPart, SearchOrder :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Calculate
application.displayalerts = true
End Sub

[M1234]

Hi Yudlugar,

This solution still didn't work. It keeps on displaying the Select Sheet dialogue box when it can't find the tab

[ragulduy]

Sorry, I don't know then. This method works ok for me. For example, in a new workbook, this macro:

Sub macro_1()
Range("A1").Formula = "='NOTFOUNDSHEETNAME'!a1"
End Sub

displays sheet select dialog box and this macro

Sub macro_1()
Application.DisplayAlerts = False
Range("A1").Formula = "='NOTFOUNDSHEETNAME'!a1"
Application.DisplayAlerts = True
End Sub

does not.

[M1234]

I even tried placing it just around the Channel_1 range and that still had no effect. It seems to find and replace fine until it gets to that cell and then anything beneath that in the named range doesn't get replaced.

[ragulduy]

Can you upload a workbook please and I'll take a look.

[M1234]

I can't upload this workbook and it'll be quite hard to replicate with data as it's linking to specific file locations

[ragulduy]

Why can't you upload it?

Just make a workbook that creates the error. It does not need to contain any real data.

[M1234]

Why can't you upload it?

Just make a workbook that creates the error. It does not need to contain any real data.

Because it's a problem when it can't find the sheet in the workbook, not that it can't find the workbook. If I upload it with the links I have or any other links then when you open it, it'll bring a box up for you to open the file, not find the sheet.

[ragulduy]

So upload a workbook, where it can't find the sheet in a workbook and gives you the dialogue box, even with displayalerts set to false.

When I replicate what you have done, I do not get the dialogue box, the solution I suggested works fine - did you try what I mentioned in post #6? Do you still get the dialogue bo x then?

[jaslake]

Hi M1234

Try this

Option Explicit

Sub FindReplace()
    On Error Resume Next
    Range("Warehouse_1,ST_1,STP_1,Channel_1,Year_1").Select
    Selection.Replace What:=Range("Old_Week"), Replacement:=Range("New_Week"), LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Calculate
    On Error GoTo 0
End Sub

[M1234]

Hi M1234

Try this

Option Explicit

Sub FindReplace()
    On Error Resume Next
    Range("Warehouse_1,ST_1,STP_1,Channel_1,Year_1").Select
    Selection.Replace What:=Range("Old_Week"), Replacement:=Range("New_Week"), LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Calculate
    On Error GoTo 0
End Sub

No luck unfortunately it still asks me to select the sheet I'm referencing