Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to go to a specific sheet

0

I have a master file that I run a macro from. It opens all the files in a folder, do some updates, then save and close.

Right now the last tab the files is on when it's saved is on one sheet but I want it to be on another (summary) sheet.

I don't want to do the straightforward Sheets("Summary").Select in case people decide to rename the sheet. (I can tell them not to rename it but people tend not to follow directions that well.)

I don't want to "Protect Workbook" to prevent a rename as others might want to add other worksheets to do other work.

I can't use the sheet CodeName since I'm running the macro from the master file and the sheet is in another file.

Any other potential solutions?

excelmacros-vba
posted by: Cathy (rep: 53) on Fri Jan 25, 2019 at 12:01 pm
Answer
Discuss

Discussion

Will the sheet always be in the same position in the workbook, as in always first or always last or always position 2?
don (rep: 1989) Jan 25, '19 at 4:45 pm
Add to Discussion

Answers

0

The reason you give for not being able to address sheets by their CodeName isn't correct. You can address any worksheet by either its name or code name. Try the code below while at least 2 workbooks are open. In my example the worksheet codenamed "Log" is not in ThisWorkbook.

Private Sub TestSheets()

    Dim Wb As Workbook
    Dim Ws As Worksheet

    For Each Wb In Application.Workbooks
        For Each Ws In Wb.Worksheets
            If Ws.CodeName = "Log" Then
                Debug.Print Ws.Name, Ws.CodeName
            End If
        Next Ws
    Next Wb
End Sub

So, knowing the CodeName, loop through all the sheets until you find the correct one and Select it.

posted by: Variatus (rep: 4889) on Sat Jan 26, 2019 at 4:26 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login