sheet1 code name vs sheets(1)

[lezawang]

Hi
I opened file A.xlsxm and wrote the code below. I ran the code and entered B.xlsx. The code below did not work until I changed the 5th line to the following

 Sheet1.Move before:=Workbooks(2).Sheets(1)

Sub abc()
    Dim x As String
    x = InputBox("file")
    Workbooks.Open (x)
    Sheet1.Move before:=Workbooks(2).Sheet1
End Sub

Why workbooks(2).sheet1 did not work? I also tried to use the Immediate window, and tried after opening B.xlsx

?Sheet1.name --> got A.xlsx

then tried

?workbooks(2).Sheet1.name --> got error message

In project window, I see both A and B have Sheet1 before the move. Thank you.

 

[Yongle]

You have tripped over a restriction on using sheet CodeName
- the code name is ALWAYS Sheet1 (the code fails if you put a workbook reference before it - ThisWorkbook.Sheet1 fails)
- Sheet1 ALWAYS refers to the sheet with codename Sheet1 in the workbook containing the code
- you cannot use a sheet CodeName when you reference a sheet that is in a different workbook to the one that the code resides

 

[Coding4Fun]

.

 

[RoryA]

- you cannot use a sheet CodeName when you reference a sheet that is in a different workbook to the one that the code resides

You can if you set a reference between the two workbooks. You then need to refer to it using projectname.codename syntax rather than just the codename.

 

[Yongle]

Thanks for that @RoryA
Any chance of an example of the syntax to use?

 

[RoryA]

It's just what I said earlier - projectname.codename and not just codename. So given a vba project named say pjTest, you'd use pjTest.Sheet1 from the referencing project.