Guru do Excel e Power BI

Olá pessoal! Acabei de me inscrever no fórum pois tenho que achar uma solução para um problema que encontrei no meu estágio.
[Em vez do Excel, estou usando o Google Sheets (mesmos recursos, fórmulas, etc... acredito que posso pedir ajuda aqui]

Tenho que criar uma pasta de trabalho com 340 abas idênticas para controle (risos, é isso mesmo!).
Para isso, tenho que ir duplicando uma de modelo. Esse modelo possui cerca de 30 hyperlinks que retornam a células da própria aba (ex: retornar ao topo, quando se está navegando no final dela - agilidade por evitar usar o scroll).
Entretanto, quando eu duplico uma aba, os hyperlinks (da nova aba) retornam às celulas da aba original, em vez de retornarem para células da nova aba criada.
Eu preciso duplicar as abas e que os hyperlinks funcionem para a nova aba duplicada

Imagino que seja algo tranquilo de fazer. Alguém pode me dar uma luz? Caso contrário ficarei anos pra finalizar isso. Rs.

Agradeço desde já.
Cordialmente,

Daniel M. Falcão.

dmfalcao escreveu: Imagino que seja algo tranquilo de fazer. Alguém pode me dar uma luz? Caso contrário ficarei anos pra finalizar isso. Rs.
.

Bom dia.
Tem razão quanto à isso. Realmente é uma tarefa bem simples usando o Excel (como se pode ver no modelo que posto em anexo).
O problema é que no "Google Sheets" a função cél não tem a opção de retornar o nome do arquivo (ela tem um leque de opções de retorno bem menor que sua equivalente no Excel) como se pode ler em:

https://support.google.com/docs/answer/3267071

Uma maneira de resolver esse problema de imediato seria (ao meu ver) criar essas 340 abas (não vou argumentar sobre esse volume de abas, vou focar no problema proposto) usando VBA e inserindo os hiperlynks em tempo de execução da Rotina.
Mas, para isso, é preciso que você anexe a planilha principal original e faça uma descrição para onde cada hiperlynk deve apontar.

Vale lembrar que não sou usuário assíduo do google sheets, logo, minha posição está limitada à tentar reproduzir esse método utilizando a função "cél" do Excel (que é o único que conheço para hiperlynks dinâmicos) no google sheets.
Talvez outro membro conheça uma técnica desconhecida por este que escreve.

Cumprimentos.
Giovani
PS:
O anexo só vai funcionar adequadamente no Excel.

Muito obrigado por se dispor a ajudar!
Aqui está uma cópia da minha planilha (nomes genéricos alterados).
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Vou explicar.
- Na primeira aba é a lista dos contratos, com hyperlinks que levam a aba das informações dos contratos e outra aba da ordem de produção referente a eles.
- São quase 170 contratos e 170 ordens de produção.
- Duplicar as abas dos contratos (001, 002, 003, 004...) é tranquilo! Meu problema são os hyperlinks das OPs (OP_001, ...)
- São dois tipos de referenciamento nas abas das OPs:
1. Externo: Algumas células puxam informações da aba anterior. Desta forma, ao alimentar a aba 001, a OP_001 é preenchida automaticamente em alguns campos (132 pra ser mais específico).
Ao duplicar, a solução que encontrei foi "Localizar e Substituir" dentro das fórmulas tudo que havia '001' por '002' (mudando a referência pra aba do contrato 002).
Isso eu faria de 1 em 1 para os 167 contratos. Não sei como fazer de uma forma mais ágil.
2. Interno: Esse que o bicho pega. São os hyperlinks que direcionam a células da mesma planilha (como vc pode ver ao examinar ela).

Eu não sou fera no excel. Ainda não comecei a estudar VBA. Então se me der uma explicação muito técnica eu não vou entender bem. Mas posso pesquisar a respeito.

Obrigado por se dispor a ajudar!
Se tiver qualquer dúvida nas minhas explicações, tento explicar melhor novamente.

Um Abraço.

Bom dia.
Feito conforme sua solicitação.
Como o arquivo ficou muito grande (8 MB) precisarei anexar link externo.
https://goo.gl/PB2qMr

Obs: Em uma semana removerei o arquivo.

Para fins de conhecimento/pesquisas futuras no fórum, segue abaixo a Rotina que escrevi:

Código: Selecionar todos

Sub replicaHyperlink()
        
        Dim a As Long
        Dim nome1 As String, nome2 As String
        Dim w1 As Worksheet, w2 As Worksheet
        Dim cel As Range
        Set w1 = Planilha4
        Set w2 = Planilha5
        
        
        For a = 2 To 167
                
                nome1 = VBA.Format$(a, "000")
                nome2 = "OP_" & nome1
                
                                
                w1.Copy after:=ActiveSheet
                ActiveSheet.Name = nome1
                
                    For Each cel In ActiveSheet.UsedRange
                    
                        If cel.Hyperlinks.Count > 0 Then
                        
                            If cel.Hyperlinks(1).SubAddress <> "LISTA!A1" Then
                            
                            
                                cel.Hyperlinks(1).SubAddress = VBA.Replace(cel.Hyperlinks(1).SubAddress, "001", nome1)
                            
                            End If
                            
                        End If
                        
                        
                        If cel.HasFormula Then
                        
                        cel.FormulaLocal = VBA.Replace(cel.FormulaLocal, "001", nome1)
                        
                        
                        
                        End If
                        
                    
                    Next cel
                
                w2.Copy after:=ActiveSheet
                ActiveSheet.Name = nome2
                
                
                        For Each cel In ActiveSheet.UsedRange
                        
                            If cel.Hyperlinks.Count > 0 Then
                            
                                If cel.Hyperlinks(1).SubAddress <> "LISTA!A1" Then
                                
                                
                                    cel.Hyperlinks(1).SubAddress = VBA.Replace(cel.Hyperlinks(1).SubAddress, "001", nome1)
                                
                                End If
                            
                            End If
                            
                            
                            
                            If cel.HasFormula Then
                        
                        cel.FormulaLocal = VBA.Replace(cel.FormulaLocal, "001", nome1)
                        
                        
                        
                        End If
                        
                        
                        Next cel
                
                
                Next a
        
        Set w1 = Nothing
        Set w2 = Nothing
        


End Sub

Nossa!

Eu nem sei como agradecer!
Na realidade eu não consegui abrir pelo google planilhas. Aparece o erro: Epa! Houve um problema na visualização deste documento.
Mas eu fiz o download e abri pelo excel e todos os links funcionaram perfeitamente. Mas, há um certo desintendimento entre o Excel e o Sheets que algumas coisas desconfiguram nessa Importação/Exportação (ex. caixas de seleção c/ formatação condicional somem e as dimensões de algumas colunas saem do padrão (que eu tinha regulado milimetricamente rs).
Ah, eu tentei jogar a do Excel baixada para o Drive e abrir com o planilhas e deu erro também.

Teria como você compartilhar esse documento do drive diretamente comigo? Então eu faria uma cópia para o meu drive e você poderia excluir o que você fez. Pode ser assim?

Só uma última dúvida: Na planilha que enviei há um PROCV nas abas 001 e 001_OP que buscam o número do contrato (na aba LISTA) e retornam o nome da turma. No modelo que você enviou esse PROCV some e é fixado TURMA 1 para todas as abas.
Seria possível, com esse código VBA, manter o PROCV?
--------------------

Edit: esqueci de colocar meu email, caso seja possível compartilhar diretamente comigo pra ver se consigo abrir no Sheets sem problemas.

dmfalcao@outlook.com

Obrigado!

dmfalcao escreveu:Teria como você compartilhar esse documento do drive diretamente comigo? Então eu faria uma cópia para o meu drive e você poderia excluir o que você fez. Pode ser assim?

Já enviei o link de compartilhamento.
Gentileza avisar quando copiar o arquivo para que eu possa apagá-lo.

dmfalcao escreveu:Na planilha que enviei há um PROCV nas abas 001 e 001_OP que buscam o número do contrato (na aba LISTA) e retornam o nome da turma.

Não encontrei essa função procv .

dmfalcao escreveu:
Ah, eu tentei jogar a do Excel baixada para o Drive e abrir com o planilhas e deu erro também.

Vale lembrar que tudo foi feito unicamente no Excel.
Em nenhum momento utilizei o google sheets .
Uma demora no carregamento deve ser normal (penso eu) dada a quantidade de hyperlinks à serem interpretados pelo google sheets.
Uma sugestão de testes seria deixar apenas umas 10 abas e tentar abrir no google sheets.

Fiz um teste com apenas 05 pares de abas e o google sheets carregou sem problemas.

Não consegui anexar a imagem, então segue o link dela:

https://drive.google.com/file/d/1nIKSiA ... sp=sharing

Agora com 340 abas.... fica a dúvida:
Será funcional ?

Obrigado mais uma vez!
Bom, antes de eu pensar em adicionar as OPs, numa versão primitiva, eu criei (dupliquei) uma por uma todas as abas das infos dos contratos (001, 002, 003, ... 167). Todas as abas linkadas à LISTA (primeira aba).
Ficou funcional, não demorava para carregar e nem direcionar quando clicava nos links.

Eu tive que apagar todas porque precisei fazer algumas alterações até a versão final e pensei em adicionar as OPs (para cada um). Acredito que as 340 no sheets funcione bem, embora tenha que desenvolver um código no próprio sheets ou criar uma por uma - uma vez que importar o arquivo do excel pro sheets vai dar erro devido ao tamanho.
Eu fiz o teste hoje de manhã com 10 pares. Importou numa boa.
Tentei colar o código que você criou nos Scripts das Macros. Não deu certo.

Eu preciso disso no Sheets pra facilitar o controle disso tudo (exemplo, funcionários com um tablet podem consultar essa planilha enquanto estiverem fora do escritório precisando de alguma informação - e atualmente gasta-se muito tempo com ligações e com a consulta - que é feita em papel: uma tabela preechida a mão para cada um dos 167 contratos).

Agradeço muito pelo seu tempo e dedicação!
Ainda vou pensar qual caminho tomar.
Mesmo que isso me dê muito trabalho, assumindo que o sheets suporte, uma vez concluído eu terei esse sistema de controle operando 100% e será bastante importante para a empresa

(Você resolveu pelo Excel, devo marcar o tópico como resolvido?)

Giovani! Acabo de ter uma epifania!
Eu havia reparado que uma pasta de trabalho do Sheets pode ter no máximo 20milhões de células.
No modelo que compartilhei (EXEMPLO--HYPERLINK E REFERENCIAMENTO), se você reparar, eu excluí todas as linhas abaixo das tabelas de forma que tivesse apenas 72 linhas cada aba (sendo que no sheets cada aba vem com 1.000 linhas por padrão). Tive que apagar, do contrário não caberia as 167 abas que eu estava desenvolvendo no começo.

Mas eu reparei o seguinte. No arquivo que você me enviou do excel (tudo completo e funcionando), não tem limite de linhas. Ou seja, quando eu importo pelo Sheets, todas as abas vão com as 1000 linhas de padrão. Desta forma, poucas abas já "atingem" o limite de 20m células.

Agora eu pergunto: Tem jeito de salvar a planilha do excel colocando um limite de 72 linhas pra todas as abas?
Acredito que esta seja exatamente a razão do arquivo importado não abrir.

SOS!

Boa noite.
Parece-me que esbarramos numa limitação do planilhas Google como pode-se ler em:

http://tetera.com.br/tecnologia/limites ... ogle-docs/

Segundo o texto existe um limite de 100 planilhas por arquivo.
Fiz um teste aqui com 48 pares de abas (da pasta de trabalho que compartilhei contigo) + as 04 principais e abriu sem problemas no navegador.

Em outra tentativa de tentar ajudá-lo com essa questão, observei (para minha surpresa) que o arquivo completo não abria nem no Excel Online.
O próprio Excel Online informou que o arquivo excedia o tamanho máximo permitido de 5 MB.
Como paliativo, converti o arquivo (localmente) para XLSB , fiz o upload novamente e o mesmo foi completamente carregado pelo Excel Online.
Infelizmente, a mesma estratégia falhou ao tentar reproduzi-la no Google Sheets.
De qualquer forma deixarei o link do arquivo no Excel Online no intuito que te seja útil.
https://1drv.ms/x/s!AstjIsWyqgg3k222DYGVzGTa4gMT

Ficou perfeito! Eu não sabia da existência do Excel Online pelo One Drive.
Só tem um pequeno detalhe (última coisa): Nas abas azuis, os campos em que no Sheets eu coloquei uma caixa de seleção (para facilitar a interface), no Excel Online aparece FALSO (ou verdadeiro se digitar - e a formatação condicional se mantém).

No excel normal há a possibilidade de inserir esses BOXes na área de Desenvolvedor.
No excel online eu não achei.

Saberia me dizer se há como fazer isso?
Se não souber ou não tiver como, tudo bem. Eu nem sei como agradecer por essa imensa ajuda!
Estou marcando o tópico como concluído.

Muito Obrigado, Giovani!

Guru do Excel e Power BI

Duplicar uma aba: Hyperlinks retornam a aba original(Sheets)

Duplicar uma aba: Hyperlinks retornam a aba original(Sheets)

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Duplicar uma aba: Hyperlinks retornam a aba original(Sheets)

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She

Duplicar uma aba: Hyperlinks retornam a aba original(Sheets)

Re: Duplicar uma aba: Hyperlinks retornam a aba original(She