CODEWITHSUNDEEP
excelvba
AdamMcMillan0815
AdamMcMillan0815

Reputation: 13

sheet cannot be found

I have a working macro which changes a linked sheet in a cell according to month. Say from April to March.

From

='C:\Data\Name\[Time.xlsx]2021-04!A1"

to

='C:\Data\Name\[Time.xlsx]2021-05!A1".

This works as long as the user remembers to add a new sheet at the beginning of each month, which is not always done in time. As a result i get a "sheet cannot be found -> choose one from below" Prompt". How can I avoid this selector and add a "table not found" string in the cell instead and move on to the next operation in the loop?

Thanks!

EDIT:

Code Added upon request:

Sub Month()

Set rngB = Range("B2:B6")
strColB = "Range("B1")
iRowB = 1

strMonth = InputBox ("Insert Month as integer","Month")
strMonth = Trim(strMonth)

For Each cellB In rngB
    cellB.Formula = "='C:Data\[" & strColB & "Time.xlsx]2021-" & strMonth & "'!B" & iRowB
    iRowB = iRowB + 1
Next cellB

End Sub

Upvotes: 1

Views: 86

Answers (1)

Toddleson
Toddleson

Reputation: 4457

Here's an easy function to test if a sheet exists prior to accessing its cells

Function SheetExists(ByVal SheetName As String, ByRef InWorkbook As Workbook) As Boolean
    On Error Resume Next
    SheetExists = Not InWorkbook.Sheets(SheetName) Is Nothing
    On Error GoTo 0
End Function

Here's how you would use it.

Sub test()
    MsgBox SheetExists("2021-04", Application.Workbooks("Time.xlsx"))
End Sub

Another example:

Sub test()
    If SheetExists("2021-04", Application.Workbooks("Time.xlsx")) Then
        'do stuff
    Else
        [a1] = "table not found"
    End If
End Sub

Edit: After the code was added to the original post. Here is an example of how to implement this function with that code:

Sub Month()

Set rngB = Range("B2:B6")
strColB = Range("B1").Text
iRowB = 1

strMonth = InputBox("Insert Month as integer", "Month")
strMonth = Trim(strMonth)

For Each cellB In rngB
    If SheetExists("2021-" & strMonth, Application.Workbooks("Time.xlsx")) Then
        cellB.Formula = "='C:Data\[" & strColB & "Time.xlsx]2021-" & strMonth & "'!B" & iRowB
    Else
        cellB.Formula = "table not found"
    End If
    iRowB = iRowB + 1
Next cellB

End Sub

Upvotes: 1

Related Questions