vba creating tabs that exceed 31 characters - error

Asked

Viewed 96 times

0

the data exceeds the character limits to name tabs in excel.

How could I fix this?

Sub compilação()

linha = 1
linha_fim = Range("A1").End(xlDown).Row

Range("B2:B" & linha_fim).Copy
Range("U1").PasteSpecial
Application.CutCopyMode = False
ActiveSheet.Range("$U$1:$U$" & linha_fim).RemoveDuplicates Columns:=1, Header:= _
    xlNo

linha_fim = Range("U1").End(xlDown).Row

While linha <= linha_fim
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = Sheets("Base").Cells(linha, 2)

    **Tamanho = Len(ActiveSheet.Name)
    If Tamanho > 30 Then
        ActiveSheet.Name = Mid(ActiveSheet.Name, Tamanho - 30, 30)
    End If
    ActiveSheet.Name = ActiveSheet**

    Sheets("Base").Range("A1:S1").Copy
    ActiveSheet.Range("A1").PasteSpecial

    linha = linha + 1
Wend

Sheets("Base").Range("U:U").Clear

linha = 2

While Sheets("Base").Cells(linha, 1) <> ""
    Sheets("Base").Range("A" & linha & ":S" & linha).Copy

    Naturezas = Sheets("Base").Cells(linha, 2)
    Sheets(Naturezas).Select
    Range("A10000").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False

    Sheets("Base").Select

    linha = linha + 1
Wend

For Each aba In ThisWorkbook.Sheets
    aba.Columns("A:S").AutoFit
Next

End Sub
excel vba excel-vba

  • This is a limitation of Excel, one of the numerous undocumented, there is code to change that. There are some ways around the problem, such as abbreviating words, etc... Or limit the number of characters to 31 and do not write in the tab name.

  • is that the comic already well with the name I need to name the tabs, and those names often exceed the character limit. You could "edit" this database at once by limiting the number of characters in the cell to 31. I think it might even work from there

1 answer

-1

I believe excel only allows names with up to 30 characters for tab names.

I don’t know what’s in these cells but you could use one of the substring functions: LEFT, MID, RIGHT. They can take part of your string. Left counting from the beginning, mid from a specified location and right from the direct.

Mid(text, initial number_character, quantity_characters)

NomePlanilhaAtual = Sheets("relatorio_despacho-1").Cells(linha, 21)
Tamanho = Len(NomePlanilhaAtual)
IF Tamanho > 30 Then
   NomePlanilhaAtual = Mid(NomePlanilhaAtual, Tamanho - 30, 30)
End If
ActiveSheet.Name = NomePlanilhaAtual

  • Thanks for the attention there brother, I’ll copy the code here did not work.

Browser other questions tagged excel vba excel-vba

You are not signed in. Login or sign up in order to post.