Guru do Excel e Power BI

Enviado: **16 Jul 2019 às 09:50**

Olá,

Preciso de ajuda na criação de um índice informativo automático.
Tenho um arquivo com as abas: Home, Vitória, Curitiba, Liverpool.
Preciso que na aba "Home" apareça listado os nomes das abas e as informações contidas nas céluas E2 e E3 das outras abas. Dessa forma, ficaria:

A1= Vitória
B1= Vitória!E2
C1= Vitória!E3
A2= Curitiba
B2= Curitiba!E2
C2= Curitiba!E3
A3= Liverpool
B3= Liverpool!E2
C3= Liverpool!E3

Com poucas abas é tranquilo fazer manualmente mas minha pasta vai ter mais de 100 abas então preciso de uma forma mais automatizada de gerar esses dados.

Desde já agradeço!

Enviado: **16 Jul 2019 às 10:53**

Anexa um exemplo que mostro para você.
Para adiantar, eu faria o seguinte/.
1) listaria em uma planilha auxiliar o nome das planilhas usando a função INFO.PASTA.TRABALHO (https://www.youtube.com/watch?v=8_Ckxz2_JXA&t=411s) no gerenciador de nomes (salvando o arquivo habilitado para macro).
2) usaria a função, DIREITA, LOCALIZAR e NÚM.CARACT para extrair somente o nome das planilhas, sem o endereço do arquivo.
3) faria um intervalo nomeado para pegar somente os nomes das planilhas de forma dinâmica usando o CONT.SE e DESLOC.
4) usaria funções INT, MOD, SE e INDIRETO para fazer a fórmula que deseja de forma automática.

Enviado: **16 Jul 2019 às 11:21**

Obrigada!

mandei um anexo exemplo como você pediu!

Enviado: **16 Jul 2019 às 11:27**

Se você quiser testar uma solução via macro, instale uma cópia do código abaixo em um módulo comum, assim:
1. copie o código daqui
2. a partir de qualquer planilha tecle 'Alt+F11' para acessar o editor de VBA
3. no menu do editor / Inserir / Módulo
4. cole o código na janela em branco que vai se abrir
5. feito! 'Alt+Q' para retornar para a planilha e testar

para rodar o código:
6. tecle 'Alt+F8' / selecione a macro correspondente / Executar, ou insira um botão na planilha e vincule-o à macro ou vincule-a a um atalho de teclado (Alt+F8 / Opções).

Código: Selecionar todos

Sub InsereDados()
 Dim ws As Worksheet
  For Each ws In ThisWorkbook.Sheets
   If ws.Name <> "Home" Then
    Sheets("Home").Cells(Rows.Count, 1).End(3)(2).Resize(, 3).Value = _
      Array(ws.Name, "=" & ws.Name & "!E2", "=" & ws.Name & "!E3")
   End If
  Next ws
End Sub

Enviado: **16 Jul 2019 às 15:57**

Deu certinho!! Show!

Mas sempre que eu aperto o botão vinculado à macro a listagem se repete. É possível bloquear isso e fazer a lista só atualizar as abas/info adicionadas?

Enviado: **16 Jul 2019 às 17:10**

https://excelmaniacos.com/2017/02/07/fu ... o-arquivo/

Enviado: **16 Jul 2019 às 17:44**

Herica escreveu: É possível ... fazer a lista só atualizar as abas/info adicionadas?

Experimente o código abaixo no lugar do anterior. Esse código irá atualizar a lista das planilhas.

Coloquei também um comando ao final do código para ordenar a lista de A a Z com base nos nomes das planilhas. Se você quiser testar basta remover o apóstrofo do início daquela linha (ela aparecerá com fonte verde ao ser colada no editor de VBA).

Código: Selecionar todos

Sub InsereDados()
 Dim ws As Worksheet
  Sheets("Home").[A:C] = ""
  For Each ws In ThisWorkbook.Sheets
   If ws.Name <> "Home" Then
    Sheets("Home").Cells(Rows.Count, 1).End(3)(2).Resize(, 3).Value = _
      Array(ws.Name, "=" & ws.Name & "!E2", "=" & ws.Name & "!E3")
   End If
  Next ws
  'Sheets("Home").[A:C].Sort Key1:=[A1], Order1:=xlAscending
End Sub

Enviado: **17 Jul 2019 às 06:57**

Bom dia Osvaldo

Tenho um problema muito semelhante razão pela qual queria aproveitar este tópico e não abrir outro já que é uma pequena variante deste e que se prende com o seguinte.
Como fazer para colocar os valores em Home mas a partir da celula A9?
Como fica a instrução:

Código: Selecionar todos

Sheets("Home").Cells(Rows.Count, 1).End(3)(2).Resize(, 3).Value = _
      Array(ws.Name, "=" & ws.Name & "!E2", "=" & ws.Name & "!E3")

Obrigado
Jorge Cabral

Enviado: **17 Jul 2019 às 08:51**

Olá, Jorge.

Experimente:

Código: Selecionar todos

 IIf(Sheets("Home").Cells(Rows.Count, 1).End(3).Row > 8, Sheets("Home"). _
     Cells(Rows.Count, 1).End(3)(2), Sheets("Home").[A9]).Resize(, 3).Value = _
      Array(ws.Name, "=" & ws.Name & "!E2", "=" & ws.Name & "!E3")

obs. se em alguma célula do intervalo A1:C8 houver dados que não devem ser apagados então substitua também a linha abaixo

Código: Selecionar todos

 Sheets("Home").[A:C] = ""

por esta

Código: Selecionar todos

 Sheets("Home").Range("A9:C" & Cells(Rows.Count, 1).End(3).Row) = ""

Enviado: **17 Jul 2019 às 09:28**

Perfeito, o SORT é que não funciona direito(?).
Obrigado e desculpem o offtopic

Enviado: **17 Jul 2019 às 10:06**

Experimente este abaixo que contém as 3 alterações antes comentadas (insere a partir de A9, não limpa A1:C8 e ordena a parir de A9:C9).

Código: Selecionar todos

Sub InsereDados()
 Dim ws As Worksheet
  If Sheets("Home").[A9] <> "" Then Sheets("Home").Range("A9:C" & Cells(Rows.Count, 1).End(3).Row) = ""
  For Each ws In ThisWorkbook.Sheets
   If ws.Name <> "Home" Then
    IIf(Sheets("Home").Cells(Rows.Count, 1).End(3).Row > 8, Sheets("Home"). _
     Cells(Rows.Count, 1).End(3)(2), Sheets("Home").[A9]).Resize(, 3).Value = _
      Array(ws.Name, "=" & ws.Name & "!E2", "=" & ws.Name & "!E3")
   End If
  Next ws
  Sheets("Home").Range("A9:C" & Cells(Rows.Count, 1).End(3).Row).Sort Key1:=[A9], Order1:=xlAscending
End Sub

Guru do Excel e Power BI

Índice de abas e outras informações

Índice de abas e outras informações

Re: Índice de abas e outras informações

Índice de abas e outras informações

Re: Índice de abas e outras informações

Re: Índice de abas e outras informações

Índice de abas e outras informações

Re: Índice de abas e outras informações

Índice de abas e outras informações

Re: Índice de abas e outras informações

Índice de abas e outras informações

Re: Índice de abas e outras informações