Ir ao conteúdo
  • Cadastre-se

Excel Eliminar Worksheets utilizando a Função Vlookup


Posts recomendados

Boas,

Precisava de ajuda para eliminar determinadas Worksheets num ficheiro excel utilizando em simultâneo a função Vlookup.

Tenho uma Sheet Geral com uma tabela, no qual tenho o nome das várias Sheets no documento.

Contudo, quero colocar na coluna A, um "x" para dizer quais as sheets que quero manter e eliminar as restantes sheets e aí utilizar a função Vlookup para encontrar o "x", ler o valor à frente na célula B e C e selecionar a Worksheet com esse nome e eliminar.

Conseguem-me ajudar?

Obrigada.

Capturar.PNG

Link para o comentário
Compartilhar em outros sites

Eliminar/excluir planilhas é possível manualmente ou via macro. Via fórmulas não (se conseguirem, eu pago a bacalhoada :P).

Experimente esta solução via macro.

Instale o código abaixo em um módulo comum, assim:
1. copie o código daqui
2. a partir de qualquer planilha do arquivo 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).

 

Sub ExcluiPlans()
 Dim ws As Worksheet, r As Long, LR As Long, pm As String, m As Long
  Application.ScreenUpdating = False
  ActiveSheet.AutoFilterMode = False
  LR = Cells(Rows.Count, 2).End(3).Row
  [A2].AutoFilter 1, ""
  For Each ws In ThisWorkbook.Worksheets
   pm = Left(ws.Name, 6)
   r = Evaluate("SUMPRODUCT((B3:B" & LR & "=" & Chr(34) & pm & Chr(34) & _
     ")*SUBTOTAL(3,OFFSET(B3,ROW(B3:B54)-ROW(B3),)))")
   If r > 0 Then
    Application.DisplayAlerts = False
     ws.Delete
    Application.DisplayAlerts = True
   End If
  Next ws
 
'Range("B3:B" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  ActiveSheet.AutoFilterMode = False
 
'[A:A] = ""
  Application.ScreenUpdating = True
End Sub

 

funcionamento - o código irá excluir as planilhas listadas e sem "x" na coluna A

 

obs.

1. ao rodar o código a planilha ativa deverá ser Lista PMM - geral

2. se desejar excluir as linhas com os nomes das planilhas excluídas remova o apóstrofo inicial da linha abaixo no código

'Range("B3:B" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete

3. se desejar limpar os "x" da coluna A remova o apóstrofo inicial da linha abaixo no código

'[A:A] = ""

 

Curiosidade - para cada obra você insere e nomeia essa quantidade de planilhas conforme a lista, ou há um Template?

Link para o comentário
Compartilhar em outros sites

@osvaldomp , Bom Dia.

 

O seu código elemina a planilha, mas, o que eu estou entendendo é que ele que colocar o X  OCULTA a planilha e tira o X RE-EXIBE a planilha.

 

Talvez usando propriedades

xlsheetvisible
xlsheethidden
xlsheetVeryhidden

 

Para ocultar ou exibir

 

Em VBA você pode usar a propriedade Worksheet.veryhidden para esconder a planilha para que ele só pode ser unhidden de dentro VBA 

activesheet.visible = xlSheetVeryHidden 
ou 
Worksheets ( "Este planilhas nome"). Visible = xlSheetVeryHidden 

Os dados ainda está disponível para VBA, apenas um usuário não pode vê-lo 

 

olha este codigo

 

Sub HideSheet()
    Dim sheet As Worksheet
    Set sheet = ActiveSheet
    ' this hides the sheet but users will be able
   ' to unhide it using the Excel UI
   sheet.Visible = xlSheetHidden

    ' this hides the sheet so that it can only be made visible using VBA
   sheet.Visible = xlSheetVeryHidden
End Sub

 

Você é o Expert em VBA então fica fácil fazer esta rotina

 

Decio

 

 

  • Amei 1
Link para o comentário
Compartilhar em outros sites

Olá, Decio.

No título deste tópico ela colocou ~~~> Eliminar Worksheets utilizando a Função Vlookup

No primeiro post ela escreveu ~~~>"... selecionar a Worksheet com esse nome e eliminar."

e depois ela escreveu em outro post ~~~> "... os separadores "Instruções e Lista PMM-Geral" devem ficar sempre no ficheiro. Esses nunca devem ser apagados."

 

Será que em Portugal os termos "eliminar" e "apagar" planilha significam ocultar planilha ?

Se sim, conforme você interpretou, também não é possível ocultar planilhas via fórmulas, como ambos estão cogitando.

Vamos aguardar a manifestação da interessada.

Link para o comentário
Compartilhar em outros sites

Bom dia,

 

Caros Osvaldo e Decio,

 

Muito agradecia com as vossas respostas. 

Hoje ri-me um pouco com o comentário do Osvaldo "Será são que em Portugal os termos "eliminar" e "apagar" planilha significam ocultar planilha ?" Que máximo.

Os termos "eliminar" e "apagar" são sinónimos, peço desculpa porque nem me apercebi que poderia dificultar a vossa compreensão.

 

Utilizei o código do  Osvaldo e funcionou na perfeição, nunca chegaria lá sozinha. Acho que já estava a dificultar demasiado.

 

Quanto ao teu código Decio, agradeço também porque me vai ajudar em outras macros.

 

Fico mesmo grata com a vossa disponibilidade e espero sempre contar com a vossa ajuda.

 

Muito obrigada mesmo.

 

Rute

Link para o comentário
Compartilhar em outros sites

Boa tarde,

 

Não existe problema @deciog.

 

 

 

Não sei se posso pedir mais uma ajuda para vocês @deciog e @osvaldomp

Eu já tenho uma macro que me permite ir buscar determinado ficheiro numa pasta e fazer as restantes alterações que quero.

 

Agora estou com o problema que é o seguinte:

- sempre que guardo um ficheiro enviado por outros colegas, ele vem sempre com nomes diferentes.

- Então precisava que a minha macro alterasse primeiro o nome do ficheiro para o nome pretendido (guardado num excel).

- O problema é que eu não guardo o nome dos ficheiros que recebo no excel por isso complicaria e não ajudaria.

 

Existe alguma forma de se puder fazer isso? 

Caso não me tenha justificado bem avisem.

Link para o comentário
Compartilhar em outros sites

@RuteFe , Bom Dia.

 

Rute, eu não programo mais em VBA, Macro, só estou ajudando a resolver problemas em formulas, explico.

 

Hoje estou aposentado, pois conheço 14 linguagem de programação principalmente VB versão 6.0 e VBA das antigas

 

Então eu deixo para os outros colegas a ajudar em VBA

 

Decio

 

  • Curtir 1
Link para o comentário
Compartilhar em outros sites

20 horas atrás, RuteFe disse:

Eu já tenho uma macro que me permite ir buscar determinado ficheiro numa pasta e fazer as restantes alterações que quero.

 

Agora estou com o problema que é o seguinte:

- sempre que guardo um ficheiro enviado por outros colegas, ele vem sempre com nomes diferentes.

- Então precisava que a minha macro alterasse primeiro o nome do ficheiro para o nome pretendido (guardado num excel).

- O problema é que eu não guardo o nome dos ficheiros que recebo no excel por isso complicaria e não ajudaria.

 

@RuteFe Eu não consegui perceber qual a relação entre as duas partes dos seus comentários.

Na primeira parte você informa que tem uma macro que faz atualizações em um certo arquivo localizado em uma certa pasta.

Na segunda parte você busca um meio de renomear um arquivo recebido.

 

A sua ideia é renomear o arquivo utilizando alguma macro já existente ou via uma nova macro ?

Por qual meio esse arquivo é recebido ? Qual o nome a ser dado ao arquivo recebido ?

Esse nome a ser dado ao arquivo recebido irá refletir na macro citada na primeira parte ?

Será oportuno ver aqui os códigos das macros já existentes.

 

Por fim você perguntou: "Existe alguma forma de se puder fazer isso? " ~~~> Fazer exatamente o que?

Link para o comentário
Compartilhar em outros sites

Boa tarde Osvaldo,

 

Em anexo envio-te o meu ficheiro excel de base onde está a macro que utilizo. 

Na folha "Controlo_Tadicionais" tenho uma coluna pintada a amarelo que são os nomes que quero dar aos ficheiros que recebo.

Na imagem anexada está definido o caminho para a pasta onde guardo os ficheiros que recebo.

Esses ficheiros deviam ter o nome que está na coluna amarela. Eu posso fazer isso manualmente, mas se existe algo forma de poder alterar por macro era ideal. Pode ser integrada ou não com esta. 

 

O importante era que me ajudasse.

 

Obrigada @osvaldomp

Caminho para os Relatórios guardados.PNG

Controlo_v0.xlsx

Link para o comentário
Compartilhar em outros sites

Olá, Rute.

Vi o seu código e não é viável incluir nele comandos para salvar os ficheiros que você recebe, já que ele trata de outras tarefas.

Melhor escrever um código específico para salvar.

 

Quanto a salvar os ficheiros que você recebe, já sabemos o nome que cada um terá (lista em amarelo) e sabemos também o local em que serão salvos ("X:\Controlo Custos\RELATÓRIOS_OBRAS\Relatório de obra_Agosto2018").

Falta agora saber onde estão esses ficheiros, como acessá-los, qual o critério para a escolha na lista, do nome que cada um receberá.

 

Se possível, descreva passo-a-passo como você faz hoje salvando manualmente tais ficheiros. Facilitaria também se você gravar uma macro dessa operação e depois colocar aqui no fórum a macro gravada..

 

Link para o comentário
Compartilhar em outros sites

Bom dia @osvaldomp,

 

O gravar eu faço manualmente porque tenho de aceder às pastas por webdrive.

 

Só precisava mesmo que a macro alterasse o nome dos ficheiros dessa pasta (fotografia já enviada) e colocasse o nome que está na coluna a amarelo (ficheiro excel).

 

Para quando correr a minha macro estarem esses nomes nos ficheiros e ela reconhecer.

 

Obrigada Osvaldo

Link para o comentário
Compartilhar em outros sites

26 minutos atrás, RuteFe disse:

Só precisava mesmo que a macro alterasse o nome dos ficheiros dessa pasta (fotografia já enviada) e colocasse o nome que está na coluna a amarelo (ficheiro excel).

 

1. o ficheiro a ser renomeado está na pasta "X:\Controlo Custos\RELATÓRIOS_OBRAS\Relatório de obra_Agosto2018" e após ser renomeado ele será salvo nessa mesma pasta, é isso?

2. qual o nome de cada ficheiro a ser renomeado, ou seja, onde está escrito o nome de cada ficheiro que deverá ser renomeado ?

3. qual o critério para escolher na lista o novo nome do ficheiro ?

Link para o comentário
Compartilhar em outros sites

Olá,

 

1. Sim, deve ficar na mesma pasta.

2. Não está, porque não sabia como copiar primeiro o nome dos ficheiros para o excel.

3. Talvez pelo Código CAOBxxxx.

adicionado 2 minutos depois

Para a opção 2 era ele procurar pelo código CAOBxxxx na pasta dos ficheiros e guardar esse nome no excel, renomear o nome e colocar o novo nome no ficheiro.

Link para o comentário
Compartilhar em outros sites

2 horas atrás, RuteFe disse:

1. Sim, deve ficar na mesma pasta. ~~~> OK :)

2. Não está, porque não sabia como copiar primeiro o nome dos ficheiros para o excel.

3. Talvez pelo Código CAOBxxxx. ~~~> "talvez" não é critério para se escolher um nome em uma lista com mais de 30 nomes; você precisa informar qual o novo nome do arquivo ou qual o critério para escolher o nome na lista :confused:

adicionado 2 minutos depois

Para a opção 2 era ele procurar pelo código CAOBxxxx na pasta dos ficheiros e guardar esse nome no excel, renomear o nome e colocar o novo nome no ficheiro. ~~~> procurar por CAOBxxxx é válido se houver somente um arquivo na pasta cujo nome comece por CAOB, pois se houver múltiplos arquivos cujo nome se inicie por CAOB é equivalente a procurar alguém de nome Manoel em Lisboa :P

 

Em resumo, o que falta para elaborar o código é um nome de arquivo para ser buscado e um nome para renomeá-lo, sem essas informações exatas eu não consigo avançar.

Link para o comentário
Compartilhar em outros sites

15 horas atrás, osvaldomp disse:

 

Em resumo, o que falta para elaborar o código é um nome de arquivo para ser buscado e um nome para renomeá-lo, sem essas informações exatas eu não consigo avançar.

 

O nome do arquivo para ir buscá-lo é o número, todas os nomes dos ficheiros têm um número a seguir ao CAOB ( por exempo: 1161) (coluna verde)

O nome que quero no ficheiro a coluna Amarela do ficheiro excel que enviei.

 

Controlo_v0.xlsx

 

Obrigada.

Link para o comentário
Compartilhar em outros sites

Ok, vou me basear pela lista de números da nova coluna J. Uma dúvida porém, todos os ficheiros têm nomes iniciados por CAOB ? Ou seja, posso buscar os ficheiros com nome igual a CAOB & nº da coluna J? Pergunto pois na coluna D, de onde você extraiu os números para formar a nova coluna J, há outros prefixos além de CAOB, como CAOG e CAU.

 

Não podemos buscar os ficheiros pelos nomes que estão na coluna D ?

Algo assim:  o ficheiro com o nome que está em D5 receberia o nome que está em K5, o ficheiro com nome igual a D6 receberia o nome de K6, ... e assim até o final da lista.

Link para o comentário
Compartilhar em outros sites

Olá Osvaldo,

 

Sim, pode se ir buscar pelos nomes da coluna D. Porque todos eles tem um código associado.

 

Mas não interfere na procura o facto de ele ter mais texto no nome do ficheiro? 

Exemplo de um nome:  "CAOB1165_relatório de obra_nº4_Ago2018" (possui a CAOB1165 + o restante texto)

 

Obrigada Osvaldo.

Link para o comentário
Compartilhar em outros sites

46 minutos atrás, RuteFe disse:

Mas não interfere na procura o facto de ele ter mais texto no nome do ficheiro? 

Exemplo de um nome:  "CAOB1165_relatório de obra_nº4_Ago2018" (possui a CAOB1165 + o restante texto)

 

 

No ficheiro que você disponibilizou no post #20, nas colunas H e I há listas de ficheiros com o caminho completo.

Por acaso uma dessas listas seria a lista de ficheiros a renomear? E o novo nome seria o que está na coluna K mesma linha ?

Link para o comentário
Compartilhar em outros sites

A coluna I é o caminho para ir buscar o ficheiro já com o nome renomeado. Nome esse que está representado na coluna K

 

A coluna H é o caminho até à pasta dos ficheiros a renomear (neste caso a mesma pasta) 

 

Pelo que pode ser usada para fazer o caminho até aos ficheiros que vão receber o nome da coluna K

 

 

 

 

 

Link para o comentário
Compartilhar em outros sites

Veja se estão corretos os dois exemplos abaixo.

 

nome atual e novo nome do primeiro ficheiro a ser buscado:

fórmula para obter o nome atual ~~~> =H5&"\"&D5

nome atual resultante da fórmula acima ~~~> X:\Controlo Custos\RELATÓRIOS_OBRAS\Relatório de obra_Ago2018\CAOB1163

fórmula para formar o novo nome ~~~> =H5&"\"&K5

novo nome resultante da fórmula acima ~~~> X:\Controlo Custos\RELATÓRIOS_OBRAS\Relatório de obra_Ago2018\CAOB1163_relatório de obra_Ago2018

 

nome atual e novo nome do segundo ficheiro a ser buscado:

fórmula para obter o nome atual ~~~> =H6&"\"&D6

nome atual resultante da fórmula acima~~~> X:\Controlo Custos\RELATÓRIOS_OBRAS\Relatório de obra_Ago2018\CAOB1164

fórmula para formar o novo nome ~~~> =H6&"\"&K6

novo nome resultante da fórmula acima ~~~> X:\Controlo Custos\RELATÓRIOS_OBRAS\Relatório de obra_Ago2018\CAOB1164_relatório de obra_Ago2018

Link para o comentário
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisa ser um usuário para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar agora

Sobre o Clube do Hardware

No ar desde 1996, o Clube do Hardware é uma das maiores, mais antigas e mais respeitadas comunidades sobre tecnologia do Brasil. Leia mais

Direitos autorais

Não permitimos a cópia ou reprodução do conteúdo do nosso site, fórum, newsletters e redes sociais, mesmo citando-se a fonte. Leia mais

×
×
  • Criar novo...