Picking tabs name in a closed spreadsheet

Question

Hello, I have a master list of documents that update itself, it does not open any file, only uses the reference to find the data, but it turns out that some of the employees are changing the name of the tab, this makes to update the spreadsheet I have to stay selecting the correct spreadsheet.

I would like there to be a previous macro that takes the name of the tab in specific so that this error does not happen.

Simplifying:

X = "='\\SERVIDOR\LOCAL\" & fld.Name & "\" & fld2.Name & "\FIT - Ficha de instrução de trabalho\[" & fld3.Name & "]Plan " & verificar_numero & "'!$H$3"

My problem is in this piece:

& "]Plan " & check_number & "'! $H$3"

"Plan" changes its name and I have no idea how to get its name without entering the spreadsheet.

Thank you.

Answer 1

I always suggest that when referring to an external spreadsheet use the CodeName instead of Name, that the user can easily change. Then you will have control of where you are looking for the data and, if applicable, even change the name of the spreadsheet/tab.

It follows below screen explaining how to search the Name and the codename of the spreadsheet:

To use in the code in question, I suggest to use as follows:

1. Search the worksheet you want using the codename
2. Install an object with this spreadsheet
3. Return the current sheet name (regardless of which one)

Below is an example of the code:

Sub Retorna_Nome_da_Planilha()

Dim PLANILHA As Worksheet

    Set PLANILHA = CodeName1

    ' Em sua fórmula irá utilizar o código abaixo.
    Debug.Print PLANILHA.Name

End Sub

Following his example:

& "]" & SPREADSHEET.Name & checkNumber & "'! $H$3"

I hope I’ve helped!

Answer 2

You can declare Worksheet in two ways that make it possible to rename tabs.

• Worksheets(1) or Sheets(1), where you use the index number of the spreadsheet, for example:

  Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)
  ws.Cells(1, 1) = "TESTE"
  

Will write TEST in the cell A1 of the first worksheet created, if you use index 2 in the second

• Worksheets("Planilha1") or Sheets("Planilha1") uses the name EXACT of the spreadsheet, therefore if it is changed errors will occur, for example, if the name of the index worksheet 3 is changed to Worksheet 1, this code will declare the spreadsheet name Worksheet 1 and index 3.

• Planilha1 with Excel in Portuguese or Sheet1 if it’s in English.

For more information on possible problems with renaming spreadsheets, read this reference in English: www.cpearson.com/excel/Renameproblems.aspx Copyright 2018, Charles H. Pearson

Check the Projects list for the sheet name:

So you can write a test like this: Planilha2.Cells(1, 1) = "TESTE"

Example:

The worksheet name is test and was the third to be created, these are the ways to write in A1.

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(3)
    ws.Cells(1, 1) = "TESTE"

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(3)
    ws.Cells(1, 1) = "TESTE"

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("teste")
    ws.Cells(1, 1) = "TESTE"

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("teste")
    ws.Cells(1, 1) = "TESTE"

    Planilha3.Cells(1, 1) = "TESTE"

Name of Spreadsheets

However, if you still want the sheet name.

For i = 1 To Sheets.Count
    Debug.Print Sheets(i).Name
Next i

The Debug.Print display can be replaced by some cell: Cells(i, 1)=

EDIT:

Use the index of the worksheet to obtain the name of the worksheet

The alternative is to check the index of the spreadsheet and get its name with the Woksheet.Name method

    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets(3)
    plan_nome = ws1.Name

    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets(3)
    plan_nome = ws2.Name

    Dim ws3 As Worksheet: Set ws3 = ThisWorkbook.Sheets("teste")
    plan_nome = ws3.Name

    Dim ws4 As Worksheet: Set ws4 = ThisWorkbook.Worksheets("teste")
    plan_nome = ws4.Name

    plan_nome Planilha3.Name

If worksheets are in another book of the same directory, just change ThisWorkbook by a Workbook statement:

Dim wb As Workbook: Set wb =  Workbooks("Pasta1.xlsx")
Dim ws As Worksheet: Set ws = wb.Sheets(3)

The code would stand:

X = "='\\SERVIDOR\LOCAL\" & fld.Name & "\" & fld2.Name & "\FIT - Ficha de instrução de trabalho\[" & fld3.Name & "]" & plan_nome & "'!$H$3"

Alternative

The other alternative is to block the exchange of the sheet name by the user, in which there are several ways to accomplish this in Google.

Answer 3

Sub Clean_column()

Range("A:A").Select
Selection.ClearContents

End Sub

Sub Pega_name_worksheet()

'-- TAKE THE NAME OF WORKSHEETS INSIDE THE WORKBOOK

Application.ScreenUpdating = False

Range("A:A").Select
Selection.ClearContents
Range("A1").Select
For i = 1 To Sheets.Count
    'Debug.Print Sheets(i).Name
    ActiveCell.Value = Sheets(i).Name
    Selection.Offset(1, 0).Select
Next i

Application.ScreenUpdating = True

End Sub

Sub Buscanomeplan_externa_mod01()

'-- FIND WORKSHEET NAMES IN ANOTHER WORKBOOK

Application.ScreenUpdating = False

Dim NPlan As String
Dim NPastaTrab1 As String
Dim NPastaTrab2 As String

'-- Workbook to be searched for Worksheets

Range("C2").Select
Range("A1").Select
NPastaTrab1 = Range("C1").Value
NPastaTrab2 = Range("C2").Value

Windows(Npastatrab2). Activate

Range("A1").Select
For i = 1 To Sheets.Count
    'Debug.Print Sheets(i).Name
    NPlan = Sheets(i).Name
    Windows(NPastaTrab1).Activate

    ActiveCell.Value = NPlan
    Selection.Offset(1, 0).Select

    Windows(NPastaTrab2).Activate

Next i

Windows(Npastatrab1). Activate

Application.Screenupdating = True

End Sub

'--- MODEL 02

Sub Buscanomeplan_externa_mod02()

'-- FIND WORKSHEET NAMES IN ANOTHER WORKBOOK

Application.ScreenUpdating = False

Dim NPlan As String
Dim NPastaTrab1 As String
Dim NPastaTrab2 As String

'-- Workbook to be searched for Worksheets

Range("C2").Select
Range("A1").Select
NPastaTrab1 = Range("C1").Value
NPastaTrab2 = Range("C2").Value

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'-- Vai para a planilha 02

Windows(NPastaTrab2).Activate

Range("A10000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Range("A10000").Select
For i = 1 To Sheets.Count
    'Debug.Print Sheets(i).Name
    NPlan = Sheets(i).Name

    ActiveCell.Value = NPlan
    Selection.Offset(1, 0).Select

Next i

Range("A10000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlUp).Select


Windows(NPastaTrab1).Activate
Range("A1").Select
ActiveSheet.Paste
    Application.CutCopyMode = False
Range("A1").Select

Application.Screenupdating = True

End Sub