Macro aumentando arquivo – Colando formatação – 2024 – VBA & Macros – Planilhando

RechBr
(@rechbr)
Posts: 19
Active Member
Topic starter
Prezados,
viewtopic.php?f=10&t=18074
Conforme o tópico que acabei fechando cedo de mais.
O Problema esta que a macro esta copiando as células com formatação e consequentemente pesando o arquivo.
Teria como colar apenas os dados? Ou entao eu teria que inserir na macro um processo para limpar a formatação?
Aproveitando, teria como eu congelar a tela em um planilha e nao ficar trocando rápido ("piscando" .selects?)
Muito obrigado, novamente!
Postado : 16/11/2015 6:33 am
MPrudencio
(@mprudencio)
Posts: 2749
Famed Member
Tenta assim na parte do codigo que faz a colagem troque por isso
Selection.PasteSpecialPaste:=xlPasteValues
E para parar o pisca pisca coloque isso no inicio do seu codigo
Application.ScreenUpdating = False
E isso no Final
Application.ScreenUpdating = True
Ficando assim
sub seu codigo()
Declarações de variaveis
Application.ScreenUpdating = False
Seu codigo
Application.ScreenUpdating = True
End sub
Marcelo Prudencio
Microsoft Excel Brasil no Facebook

"Começar já é a metade do caminho."
Autor Desconhecido
Simplifica que simples fica.
Nicole Tomazella.

"O Simples é Sempre Melhor Que o Complicado"
Jorge Paulo Lemann.
Postado : 16/11/2015 6:50 am
RechBr
(@rechbr)
Posts: 19
Active Member
Topic starter
Tava analisando aqui e o fato de nao piscar nao vai influenciar no código, pois o mesmo usa "activesheet"?
Valeu.
EDIT:
Outra solução pode ser
Selecionar os A:M da linha 2 ate o fim e limpar a formatação na planilha "DADOS" antes de começar tudo também resolverá meu problema.
Não sei qual seria melhor/mais facil.
Abraço
Sub Calcular()
'
' Calcular Macro
'

'
    Application.ScreenUpdating = False
    
    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
    
    Application.ScreenUpdating = True
End Sub
Postado : 16/11/2015 7:19 am
Macro aumentando arquivo - Colando formatação
