Jump to content

preciso de ajuda para ativar esta formula ela organiza as planilias


Recommended Posts

ela organiza as planilia=

VBA - Create an Index (Summary, Table of Contents) Sheet Macro

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

 

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

Sub GenIndexSheet()
    Dim Ws As Worksheet
    Dim i As Long: i = 1
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Create an Index Sheet. If already existing, clear it.
    On Error Resume Next
    Set Ws = Worksheets("Index")
    If Err.Number = 0 Then
        Worksheets("Index").ClearContents
        Else
        On Error GoTo 0
        Worksheets.Add(Before:=Worksheets(1)).Name = "Index"
    End If
    
    Worksheets("Index").Activate
    Range("A1") = "Index"
    Range("A1").Font.Bold = True
    Range("A1").Font.Size = 20
    
    For Each Ws In Worksheets
        If Ws.Name <> "Index" Then
            i = i + 1
            Ws.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
        End If
    Next Ws
    
    Worksheets("Index").Columns(1).AutoFit
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
  • Thanks 1
Link to comment
Share on other sites

  • 2 weeks later...
8 horas atrás, sorel disse:

ola substantivo, ok =http://www.mediafire.com/file/8tn5ub31j2bj5d7/index.xlsx/file

SUBS. o objetivo é ter na mao muitas planilia tipo numa pasta com abrir e ver as planilia

 consegue fazer ? por favor

Caro @sorel;

 

Não há nada para ser feito no codigo " que ja esta funcional "  basta abrir o excell , precionar alt + F11, para entrar vba,

e seguir os passos da imagem abaixo:

Spoiler

INDEX - SOREL.png

 

 

Técnica e Investimento, Sempre!!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...