sorel Posted June 17, 2020 Share Posted June 17, 2020 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 1 Quote Link to comment Share on other sites More sharing options...
Substantivo Posted June 28, 2020 Share Posted June 28, 2020 Para que esta macro funcione você precisa ter uma planilha com o nome index. Quote Link to comment Share on other sites More sharing options...
sorel Posted June 28, 2020 Author Share Posted June 28, 2020 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 Quote Link to comment Share on other sites More sharing options...
FF882007 Posted June 28, 2020 Share Posted June 28, 2020 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 Técnica e Investimento, Sempre!! Quote Link to comment Share on other sites More sharing options...
sorel Posted June 29, 2020 Author Share Posted June 29, 2020 ok,FF88. OBJETIVO É TER NA MAO, AS PLANILIAS PARA UMA RAPIDA CONSULTA PARA CRUZAMENTOS DE ESTRATEGIASE PADROES em determinados setores NA MATRIZE Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.