VBA worksheet references

[Robert Crandal]

VBA doesn't seem to like when I use the following code:

Workbooks("mybook").Sheet1.Range("A1").Value = 100

However, the code seems to work when I remove the part that says:

Workbooks("mybook").

How can I fix this reference so that I am able to use my VBA "Sheet1"
reference and specify the workbook at the same time?

thank u

 

[J_Knowles]

Workbooks("mybook.xls").Worksheets("Sheet1").Range("A1").Value = 100

HTH

 

[Robert Crandal]

I wanted to avoid using the Worksheets("Sheet1") method
because this references the string name on the sheet tab.
Therefore, if a user renames "Sheet1" on the tab to something
else, then your code below will NOT work.

If you want to refer to first sheet on your workbook, without
referring to the string on the tab, you can use the code below:

Sheet1.Range("A1").Value = 100

How would you specificy a workbook using this notation above??

 

[Tim Williams]

Sub Tester()
Dim wb As Workbook, sht As Worksheet
Set wb = Workbooks("Book2.xls")

Set sht = GetSheetByCodeName(wb, "Sheet1")
If Not sht Is Nothing Then
MsgBox sht.Range("A1").Value
Else
MsgBox "No sheet with codename 'Sheet1'"
End If

End Sub

Function GetSheetByCodeName(wb As Workbook, sName As String)
Dim s As Worksheet, rv As Worksheet
For Each s In wb.Worksheets
If s.CodeName = sName Then
Set rv = s
Exit For
End If
Next s
Set GetSheetByCodeName = rv
End Function


Tim

 

[Chip Pearson]

You are attempting to mix two object chains that really aren't
intended to be mixed. VBA itself knows of a worksheet only via its
CodeName, which as you correctly state doesn't change if you change
the name of the worksheet. But you're trying to mix that with an
object known only to Excel, not VBA, namely a Workbook. There is no
particularly good remedy. If your primary goal is to prevent problems
that may occur when the user changes a worksheet name, use a defined
name on that sheet and then use the Worksheet property of the Range to
get the sheet and then from the sheet get some cell. E.g.,


Debug.Print Range("TheName").Worksheet.Range("A1").Value

Here, the range A1 will point to the worksheet on which the name
TheName resides.

If you use the codename to reference a sheet, as in

Sheet1.Range("A1").Value = 1234

That sheet will always refer to the ThisWorkbook workbook. You cannot
"redirect" the Sheet1 reference to another workbook.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Rick Rothstein]

You might be able to do what you want like this...

Dim CN As String, WS As Worksheet
......
......
CN = "Sheet1"
For Each WS In Workbooks("RicksSaveAsTest.xls").Worksheets
If WS.CodeName = "Sheet1" Then Exit For
Next

After running the above code, WS (as long as it is in scope) will be set to
the worksheet with CodeName "Sheet1" no matter what the user has renamed it.
You can see this be executing this line after the Next statement...

MsgBox "Current sheet name for CodeName 'Sheet1': " & WS.Name & _
vbLf & "Contained in Workbook: " & WS.Parent.Name

This statement shows that as long as WS is in scope, you can use WS to refer
to the worksheet with CodeName "Sheet1".