Macro aumentando o tamanho do arquivo

RechBr

(@rechbr)

Posts: 19

Active Member

Topic starter

Prezados,

Sei que o assunto e muito recorrente mas realmente tentei de todas as formas mesmo lendo alguns tópicos isso não foi possível.

Atualmente o arquivo se encontra com 64MB tornando impossivel o uso nas máquinas empresariais. A macro basicamente copia dados de uma planilha para outras.

Não sei se é possivel ajuda somente com o código, envio do arquivo fica complicado pelo seu tamanho e upload lento.

Obrigado desde já.

Sub Calcular()
'
' Calcular Macro
''
    Sheets("MP002").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP002"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP002").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveWindow.SmallScroll Down:=-15
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Sheets("MP005").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP005"
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP005").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP006").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP006"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP006").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP007").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP007"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveCell.FormulaR1C1 = "c"
    With ActiveCell.Characters(Start:=1, Length:=1).Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Sheets("DADOS").Select
    Selection.Copy
    Sheets("MP007").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=-9
    Sheets("DADOS").Select
    Range("A1").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Sheets("MP010").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP010"
    Sheets("DADOS").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP010").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP011").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP011"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP011").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP013").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP013"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP013").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP029").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP029"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP029").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP038").Select
    ActiveWindow.SmallScroll Down:=-3
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP038"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP038").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP040").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP040"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP040").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP042").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP042"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP042").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-3
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP054").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP054"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP054").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP071").Select
    Columns("A:M").Select
    Selection.ClearContents
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP071"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP071").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveWindow.SmallScroll Down:=-15
    Sheets("MP084").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP084"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP084").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("MP086").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP088"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MP086").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP132").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP132"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP132").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP174").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP174"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP174").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP186").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP186"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP186").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP196").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP196"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP196").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP193").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP193"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP193").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP199").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP199"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP199").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP200").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP200"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP200").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP222").Select
    Columns("A:M").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2, Criteria1:="MP222"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MP222").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("DADOS").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$P$506").AutoFilter Field:=2
    Range("A1").Select
    Sheets("Painel").Select
End Sub

Postado : 10/11/2015 7:56 am

Fernando Fernandes

(@fernandofernandes)

Posts: 43750

Illustrious Member

Por favor, compacte e disponibilize seu arquivo para eu identificar o que o torna enorme assim.
Daí fica mais fácil procurar o problema nesta macro gravada.

FF

p.s.: ajude a gente a te ajudar, e por favor use ZIP ou RAR. Galera que usa 7z demora mais pra ter respostas pq no meio corporativo, é mais comum o zip mesmo!

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

Postado : 10/11/2015 8:03 am

Fernando Fernandes

(@fernandofernandes)

Posts: 43750

Illustrious Member

RechBR

Tá cheio de Select, isso faz ficar pesado e tremer a tela na execução.

[]s

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

Postado : 10/11/2015 9:30 am

Fernando Fernandes

(@fernandofernandes)

Posts: 43750

Illustrious Member

Mas não justifica que tamanho do arquivo...

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

Postado : 10/11/2015 9:33 am

RechBr

(@rechbr)

Posts: 19

Active Member

Topic starter

É basicamente para recortar de uma planilha e ir colando em outras...

estou tentando upar o arquivo aqui sem sucesso. vou arrumar um link de download em instantes

Postado : 11/11/2015 7:18 am

RechBr

(@rechbr)

Posts: 19

Active Member

Topic starter

Segue o link. Aqui no proprio forum so consegui upar 7z que o arquivo fica com 4MB em formato .zip fica com 13MB e da erro no upload...

arquivo convertido .zip
http://dw.convertfiles.com/files/092172 ... grande.zip

Postado : 11/11/2015 7:44 am

RechBr

(@rechbr)

Posts: 19

Active Member

Topic starter

Por favor, movam para o área correta. obrigado.

Vou mandar um link com a versao zip ate o final do dia

Postado : 13/11/2015 5:49 am

EdsonBR

(@edsonbr)

Posts: 1057

Noble Member

Olá, RechBr

Dei uma analisada em sua planilha, realmente bastante pesada, e fui por eliminação:
Hipótese 1: A vilã seria a macro?
- Verificação 1.1: eliminar a macro e salvar sem ela.
- Resultado: Ao invés de diminuir o tamanho do arquivo, ele aumentou! Foi de quase 66MB para 71,5MB - refutada
- Verificação 1.2: salvar como xlsx, que é o formato de planilha sem macro, ao invés de xlsm:
- Resultado: permaneceu com 71,5MB - refutada
- Portanto Hipótese 1 refutada.

Hipótese 2: Talvez os gráficos da planilha "Histórico" estejam aumentando o tamanho?
- Verificação 2.1: eliminar todos os gráficos e salvar
- Resultado: diminuiu somente 47 KB - refutada

Hipótese 3: Seriam as formatações as vilãs?
- Verificação 3.1: eliminar todas as formatações da planilha "Painel", que parecia ser a mais formatada e salvar
- Resultado: diminuiu somente 7 KB - refutada
- Verificação 3.2: eliminar todas as formatações da planilha "Dados"
- Resultado: diminuiu somente 4 KB - refutada
- Verificação 3.3: idem, planilha "Indicadores"
- Resultado: diminuiu somente 3 KB - refutada
- Verificação 3.4: idem, planilha "Histórico"
- Resultado: diminuiu somente 2,5 KB - refutada
- Verificação 3.5: eliminar todas as formatações das planilhas "MP002" a "MP222"
- Resultado: O tamanho total da pasta de trabalho ficou em somente 377KB - Hipótese Confirmada!

Portanto, o problema é de formatação mesmo na série de planilhas MPxxx. Penso q vc teria q analisá-las para ver o que poderia ser feito.
Veja as dicas dos nossos colegas aqui mesmo p/ ter uma noção disso - fernando.fernandes http://www.planilhando.com.br/forum/viewtopic.php?f=28&t=8862 e Eron http://www.planilhando.com.br/forum/viewtopic.php?f=28&t=3023