Syntax to define sheet name to use in VBA

[Guest]

Thanks for all of your help with VBA. I have yet another question:

Currently, I've been copying data from a pre-defined sheet number in
multiple workbooks. I'd like to be able to identify the worksheet name to
be copied from. I have this:

Set sourceRange = mybook.Worksheets(SheetNumber).Range("A25:IV45")

And want to put the worksheet name in instead. Suggestions?

Also, if there is no such sheet name in the workbook, what should I do to
deal with the errors.

Thanks,
Barb Reinhardt

 

[Guest]

The first part is easy (SheetName would be a string containing the name):
Set sourceRange = mybook.Worksheets(SheetName).Range("A25:IV45")

As for error handling: I would agree it is better to use the sheet name and
not number, expecially if you cannot be absolutely certain that the sheet
number you refer to will always remain the sam (and, in general, you cannot
assume this).

There are two approaches, one "quick and dirty" and the other one more
rigorous
1) - easier way:
On Error Goto SheetNameError
Set sourceRange = mybook.Worksheets(SheetName).Range("A25:IV45")
' rest or your code would go here
Exit Sub

SheetNameError:
MsgBox "Worksheet " & SheetName & " not found!"
' Or whatever you need to do if there is no sheet with the name you supplied
End Sub

2) A bit better (IMHO)
Dim CheckSheet as Worksheet, SheetFound as Boolean
SheetFound = False
For Each CheckSheet in mybook.Worksheets
SheetFound = SheetFound Or (CheckSheet.Name = SheetName)
Next CheckSheet
If SheetFound Then
' Code here can assume sheetname exists
Else
' No sheet of that name
End If

 

[Guest]

Thanks! I'm now having issues where I'm being asked if I want to update
links as I open each workbook. How do I disable that and have it not update
the links?