Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
Macro aumentando ar...
 
Notifications
Clear all

Macro aumentando arquivo - Colando formatação

 
2024 - VBA & Macros
Post Recente por RechBr 9 anos atrás
3 Posts
2 Usuários
0 Reactions
867 Visualizações
RSS
 RechBr
(@rechbr)
Posts: 0
New 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: 0
New 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

 
Postado : 16/11/2015 6:50 am
 RechBr
(@rechbr)
Posts: 0
New 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
Forum Jump:
  Tópico Anterior
Próximo Tópico