Easy one (I hope) Using Sheet Names to reference sheets in other Workbooks

[PaxDak]

I have a macro that checks if a workbook is open,
if its not, it opens it up.

Then I'm trying to activate a certain sheet. I can't
be sure that the worksheet that i want is the active sheet,
or if someone changed the worksheet name. So I'm trying
to use the VBA worksheet name property.
Then it selects all cells on the sheet and copies it to ThisWorkBook.

BUT, I can't get it to select the right worksheet.

sht_Comments.Select
' This works if macro is in MyFile.xls,

'but this doesn't work from another Workbook:
Workbooks("MyFile.xls").Activate
sht_DB_Comments.Select

Excel will activate the correct workbook, but not the desired sheet?
My macro to open the workbook is below. If any one sees a better/
more efficient way to write that macro, comments are welcome!

Any Help would be appreciated.

Thanks,
Pax


Sub OpenCommentWkbook()
Dim wkbk As Workbook

str_Comment_FileName = "MyFile.xls"

'Get FilePath of this workbook
str_FilePath = ThisWorkbook.Path

'Find Parent Directory Name
' This is where MyFile should be
For i = Len(str_FilePath) To 1 Step -1
If Mid(str_FilePath, i, 1) = "\" Then
str_FilePath = Left(str_FilePath, i)
i = 0
End If
Next i

On Error Resume Next
Set wkbk = Workbooks(str_RCC_FileName)
On Error GoTo 0
If wkbk Is Nothing Then
'Workbook is not open
' Try to open the Comment file
If Dir(str_FilePath & str_Comment_FileName) = "" Then
' file doesn't exist
MsgBox "The file could not be found."
End
Else
Workbooks.Open str_FilePath & str_Comment_FileName
End If
End If

'Comment Workbook is open

'Copy Comments
Workbooks(str_RCC_FileName).Activate
sht_DB_Comments.Select
Cells.Select
Selection.Copy
ThisWorkbook.Activate
sht_DB_Comments.Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


'Close Comment Workbook
Workbooks(str_Comment_FileName).Close SaveChanges:=False
End Sub

 

[Don Guillett]

sheets("mysheet").select

 

[PaxDak]

Thanks Don,
But that works if the Worksheet name (from within Excel Worksheet view)
is set to "mysheet". The end user might rename the sheet to "mynewsheet",
so thats why I was trying to use the worksheet name that is set in the
properties window within VBA.

 

[Chip Pearson]

You can use the code name of the sheet directly in VBA. E.g,

Sheet1.Select

It doesn't matter if the user has renamed the sheet. The code
name remains the same.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[PaxDak]

But I'm having problems calling the sheet using the code name
(if its in another workbook)

Sheet1.Select
- > Works great if Sheet1 resides in Myfile.xls,
which is the workbook is executing the code

but I can't get Myfile2.xls to select Sheet1 in MyFile.xls.
The following doesn't seem to work from MyFile2.xls:

Workbooks("MyFile.xls").Activate
Sheet1.Select