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

Here's how to find the spreadsheet Name and Codename:

Tousethecodeinquestion,Isuggestusingitasfollows:

Hereisanexampleofthecode:

SubRetorna_Nome_da_Planilha()DimPLANILHAAsWorksheetSetPLANILHA=CodeName1'Emsuafórmulairáutilizarocódigoabaixo.Debug.PrintPLANILHA.NameEndSub

Followingyourexample:

  

&"]" & PLANILHA.Name & check_number & "" $ H $ 3 "

I hope I have helped!

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

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

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

Will write TEST on cell A1 of First created spreadsheet, If you use index 2 on Second

• Worksheets("Planilha1") or Sheets("Planilha1") uses the EXACT name of the worksheet, so if it is changed errors will occur, for example if the name of the Index worksheet 3 is changed to Sheet1, this code will declare the worksheet name Sheet1 and index3.

• Planilha1 with Excel in Portuguese or Sheet1 if in English.

Check the Projects list for the worksheet name:

Soyoucanwriteatestlikethis:Planilha2.Cells(1,1)="TESTE"

Example:

The name of the worksheet is test and was the third one 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"

Spreadsheet Name

However, if you still want the names of the spreadsheets.

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

% display% can be replaced by some cell: Debug.Print

EDIT:

Use the index of the worksheet to get its name

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

    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 the worksheets are in another workbook in the same directory, just change Cells(i, 1)= for a Workbook declaration:

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

The code would look like:

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

Another alternative

The other alternative is to block the exchange of the user's spreadsheet name, where there are several ways to do this in Google.