Guru do Excel e Power BI

Enviado: **01 Nov 2019 às 08:42**

olá. Tenho uma planilha extremamente pesada que leva bastante tempo para abrir e calcular, nela existem vários vínculos e colunas condicionais. Gostaria de fazer uma versão "leve" dessa mesma planilha que transforma todos os condicionais e vínculos em valores preservando a mesma formatação.

Estou tentando usar o código:
Sub OpenWorkbook()

Workbooks.Open "diretório do meu arquivo"
Workbooks.Open"todos os vínculos do meu arquivo"

End Sub

Workbooks("meu arquivo.xlsx").Worksheets("Dados").Range("~tabela 1- (meus dados estão confinados numa tabela mas não sei fazer a referência correta)~").Copy
Workbooks("versão leve.xlsm").Worksheets("Dados").Range("A2").PasteSpecial Paste:=xlPasteValues

Sub CloseWorkbook()
'Close a workbook

'Workbooks("meu arquivo.xlsx").Close SaveChanges:=False
'Workbooks("todos os vínculos do meu arquivo").Close SaveChanges:=False
End Sub

Tenho algumas dúvidas, principalmente em referenciar as tabelas para a cópia. Além disso meu arquivo tem pelo menos mais umas 5 abas de dados com umas 10 tabelas auxiliares nelas. minha dúvida é: como digitar todas as referências delas de maneira eficiente?
também queria que ficasse registrada na versão leve quando foi a última atualização.
Ficarei extremamente grato se alguém puder me ajudar.
OBS: Meu arquivo pesa uns 200 megas por isso que eu não pude anexar aqui

Enviado: **01 Nov 2019 às 10:53**

Olá,

A planilha anexa tem mais de 400mil células preenchidas, sendo mais da metade fórmulas ou funções.

Ela tem uma macro no módulo2, que deve ser instalada na planilha Pesada. Ao acionada, sua primeira ação é salvar a planilha com o nome de LEVE, e em seguida transforma todas as fórmulas, de todas as planilhas, em valores. Depois salva e encerra.

Isso resolve parte do teu problema.

A outra parte, a das formatações condicionais, pelo que eu sei, é bem mais complicado.

A macro que fará essa transformação, para cada uma das células da planilha, terá que ler as condicionais daquela célula , interpretar cada uma das condições, "executar" cada condição para ver foi ou não atendida, e caso tenha sido, formatar a célula com a formatação daquela condição. Um detalhe é que para "executar" a condição", ela deve ser traduzida (E via AND, OU vira OR, etc), pois provavelmente está em português, e para ser executada (EVALUATE no VBA) deve estar em inglês.

Uma saida é tentar exportar a planilha para algum outro formato (PDF, por exemplo) e depois tentar transformar de volta em Excel. Há algum temposoube de um caso que a planilha foi salva em HTML e aberta no LibreOffice.

O melhor é procurar alguma rotina já pronta na net.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Enviado: **04 Nov 2019 às 08:56**

o problema é que tem algumas tabelas dinâmicas no meu arquivo e da erro de execução com o macro, sabe como proceder nesses casos?

Enviado: **04 Nov 2019 às 22:55**

Olá Fagundes,

Temos 2 caminhos a seguir: ou você anexa a planilha (ou uma cópia reduzida, mas com todos os elementos), ou você descreve muito bem a situação da planilha, quais componentes têm, qual é o erro que está dando, em qual linha da macro, etc, etc, etc. Se não for de uma dessas duas maneiras, qualquer resposta que você receba, pode ser que sirva, pode ser que não, a sorte é quem vai decidir.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Enviado: **05 Nov 2019 às 10:46**

Vou explicar melhor. Coloquei o código da Macro da Planilha Pesada que você encaminhou no meu projeto. Quando executo acontece essa mensagem de erro:

Quando Clico em depurar acontece o seguinte:

Como o código é pequeno, não tenho muito a depurar.

A causa é a presença de tabelas dinâmicas no meu arquivo, aqui está a lista de abas que uso:

A legenda das abas é: Azul, presença de tabelas dinâmicas
Amarelo, base de dados
Preto, auxiliares
Laranja, Tabelas de resumo a partir dos dados.

Explicando assim melhorou o entendimento?

Enviado: **05 Nov 2019 às 22:11**

Olá Fagundes,

Sim, entendi melhor.

Tente o código abaixo.

Código: Selecionar todos

Sub Macro1()
    PastaWi = ThisWorkbook.Path & "\"                     'Pasta do Windows
    PastaWB = ThisWorkbook.Name                           'Pasta Workbook
    PastaNo = Mid(PastaWB, 1, InStrRev(PastaWB, ".") - 1) 'Nome     da Pasta de trabalho
    PastaEx = Mid(Arqu, InStrRev(Arqu, ".") + 1)          'Extensão da Pasta de trabalho
    ActiveWorkbook.SaveAs Filename:=PastaWi & PastaNo & "-LEVE" & PastaEx, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        
    'Adiciona uma nova Sheet temporáriana na pasta de trabalho
    Set AquiP = ActiveSheet
    Dim NovaPlan As Worksheet
    Set NovaPlan = Worksheets.Add
    NovaNome = NovaPlan.Name
        
    For Each Plan In ThisWorkbook.Sheets
        Plan.Select
        Set AquiR = ActiveCell
        For Each TD In ActiveSheet.PivotTables
            TDRng = TD.TableRange1.Address
            
            TD.TableRange1.Copy
            NovaPlan.Select
            Range("A1").PasteSpecial Paste:=xlPasteFormats, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Range("A1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            Selection.Copy
            Plan.Select
            TD.TableRange1.PasteSpecial Paste:=xlPasteFormats, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            TD.TableRange1.PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next
        Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Value = _
        Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Value2
        AquiR.Select
    Next
    
    Application.DisplayAlerts = False:    NovaPlan.Delete
    Application.DisplayAlerts = True
    AquiP.Select
    ThisWorkbook.Save
    Set AquiP = Nothing
    Set AquiR = Nothing
    Set NovaPlan = Nothing
End Sub

Não sabia o que você pretendia fazer com as tabelas dinâmicas, então as transformei em valores também (deixaram de ser tabelas dinâmicas). A outra possibilidade é que continuem a ser tabelas dinâmicas. Se for essa a preferência, avise que eu altero a macro.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Enviado: **06 Nov 2019 às 11:52**

E gostaria que as tabelas dinâmicas continuassem a existir, pode me mandar o código com essa alteração?
[EDIT]
Quando executo o macro que você mandou na última mensagem ainda aparece a mensagem de erro nas tabelas dinâmicas

Enviado: **07 Nov 2019 às 11:13**

olá Fagundes,

Como você não deu detalhes do erro que persiste, e como a macro teve que ser mudada para preservar as tabelas dinâmicas, vamos desconsiderar o erro que deu e testar a nova planilha. Segue anexa.

Teste rigorosamente e dê retorno.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Enviado: **11 Nov 2019 às 08:54**

Agradeço aos esforços e desculpe a demora em dar um feedback, esse final de semana eu viajei e não pude testar a macro.
Depois de muitos testes ainda não funciona

Acho que sei o motivo, meus dados sempre são formatados como tabelas e as fontes das tabelas dinâmicas são esses dados confinados nas tabelas, quando a macro cola os valores os dados deixam de ser tabelas e destroem a referência da tabela dinâmica dando erro na macro.
Saber o diagnóstico é uma coisa, resolver o problema é outra

Segue sua planilha pesada B com algumas pequenas alterações que eu fiz para mostrar como o erro ainda existe.

Enviado: **11 Nov 2019 às 10:02**

Olá Fagundes,

ihanfagundes escreveu:Depois de muitos testes ainda não funciona

Não funciona, mas agora por outros motivos, o que significa que os obstáculos estão sendo superados!!

Como estamos programando no escuro, sem ter a planilha real, estamos sujeitos a isso, mas o lado bom é que esse novo problema creio que também está superado.

Teste a planilha anexa e me diga o resultado.

ihanfagundes escreveu:...meus dados sempre são formatados como tabelas e as fontes das tabelas dinâmicas

Se é assim, não creio que vá diminuir significativamente o tamanho da planilha, e consequentemente tempo de abertura e salvamento, com tudo isso que estamos fazendo. Creio que entre 10 e 20% de redução, mas ... você que tem a planilha, depois que funcionar me conte de quanto pra quanto reduziu.

Terá sim resultados em tempo de cálculo.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Enviado: **11 Nov 2019 às 13:43**

Parece que ta tudo funcionando como eu pensei

MUITO obrigado Jimmy.
Vou testar mais vezes e fazer algumas alterações com o passar do tempo, em breve eu dou meu feedback!

Guru do Excel e Power BI

Macro para copiar dados como valores de outra planilha.

Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.

Re: Macro para copiar dados como valores de outra planilha.