Deletar imagens em várias planilhas

Valdecirrk

07-10-2013 15:57:49

Boa tarde

Tenho 100 planilhas e no posição no canto superior esquerdo, se encontra a figura que quero deletar.

Bom não quero passar uma por uma deletando. Tem como desenvolver uma macro para apagar todas as figuras nas 100 planilhas?

webmaster

07-10-2013 18:35:03

Valdecirrk,

Primeiro, grave a macro deletando a figura de uma planilha. Cole ela aqui e tentamos automatizar.

Att

Valdecirrk

08-10-2013 09:51:21

webmaster escreveu:Valdecirrk,

Primeiro, grave a macro deletando a figura de uma planilha. Cole ela aqui e tentamos automatizar.

Att

em cada planilha tem a mesma imagem uma sobre a outra, segue a macro.


Sub Macro1()
'
' Macro1 Macro
' Macro gravada em 8/10/2013 por weg
'

'
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 17", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 3")).Select False
ActiveSheet.Shapes.Range(Array("Picture 4", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 17", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 3")).Select False
ActiveSheet.Shapes.Range(Array("Picture 4", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 17", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
"Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 17", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
"Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 17")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 2", "Picture 3", "Picture 4", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 17", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
"Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 17", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 3")).Select False
ActiveSheet.Shapes.Range(Array("Picture 4", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 17", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 2", "Picture 3", _
"Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 17" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 17", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
"Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 17", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
"Picture 3" _
, "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
, "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 17")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
, "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
, "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
ActiveSheet.Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
"Picture 1" _
, "Picture 1", "Picture 1")).Select False
Selection.Delete
End Sub

webmaster

08-10-2013 13:23:03

Colega,

A macro ficou um pouco grande demais. Tente acertar na primeira e grave de novo, ou vai ser complicado de automatizar.

Att

Valdecirrk

08-10-2013 13:57:07

webmaster escreveu:Colega,

A macro ficou um pouco grande demais. Tente acertar na primeira e grave de novo, ou vai ser complicado de automatizar.

Att



é que tem uma figura sobre a outra, eu gravei a macro, selecionei todas e deletei, ai parei a macro e ficou isso ai.

webmaster

08-10-2013 19:10:35

Colega,

Só tendo a planilha para certificar, mas deve funcionar:

Código: Selecionar todos

Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro gravada em 8/10/2013 por weg
    '
    
    '
    For Each plan In ThisWorkbook.Worksheets
        On Error Resume Next
        With plan
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 17", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 3")).Select False
            .Shapes.Range(Array("Picture 4", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 17", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 3")).Select False
            .Shapes.Range(Array("Picture 4", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 17", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
            "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 17", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
            "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 17")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 2", "Picture 3", "Picture 4", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 17", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
            "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 17", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 3")).Select False
            .Shapes.Range(Array("Picture 4", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 17", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 2", "Picture 3", _
            "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 17" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 17", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 2", "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
            "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 17", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 2", _
            "Picture 3" _
            , "Picture 4", "Picture 1", "Picture 1", "Picture 1" _
            , "Picture 1", "Picture 2", "Picture 3", "Picture 4" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 17")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 2", "Picture 3", "Picture 4", "Picture 1" _
            , "Picture 1", "Picture 1", "Picture 1", "Picture 2" _
            , "Picture 3", "Picture 4", "Picture 1", "Picture 1")).Select False
            .Shapes.Range(Array("Picture 1", "Picture 1", "Picture 1", _
            "Picture 1" _
            , "Picture 1", "Picture 1")).Select False
            Selection.Delete
        End With
    Next plan
End Sub
Abraços