Vídeo recomendado
https://youtu.be/diWPPPhW-9E

Macro copiar e colar com criterio

Dúvidas gerais sobre Excel
fabiosousasa
Acabou de chegar
Acabou de chegar
Mensagens: 2
Registrado em: Sex Out 16, 2020 5:22 am

Macro copiar e colar com criterio

Mensagem por fabiosousasa »

boas pessoal,
criei esta macro para copiar e colar de planilha certa informação que preciso para colar noutra planilha para trabalhar essa informação.
O problema é que sempre que alguem apaga uma linha ou acrescenta outra linha a macro desconfigura se toda.
o que precisava era de copiar com um critério.

A
1 MODULO 1
2 345
3 355
4 463546
5 MODULO 2
6 424
7 5345
8 24234

EU PRECISO DE COPIAR OS VALORES QUE ESTÃO ABAIXO DO MODULO 1 ATÉ AO MODULO 2 E DO MODULO 2 ATÉ AO MODULO 3 E ASSIM SUCESSIVAMENTE. É POSSIVEL FAZER ISSO?COPIAR ATÉ APARECER MODULO 2?

esta é a macro que eu criei, onde eu defino o espaço que copio

Código: Selecionar todos

Sub Macro1()
'
' Macro1 Macro
'
'
Range("B4:M2000").ClearFormats

Range("B4:M2000").ClearContents


Sheets("Autoclave 1").Select
Range("A5:L30").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B3").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A31:L58").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B35").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A59:L81").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B65").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A82:L107").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B95").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A108:L135").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B125").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A136:L159").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B155").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("B3:Q3").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B35:Q35").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B65:Q65").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N65"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B95:Q95").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N95"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B125:Q125").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N125"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B155:Q155").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N155"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B155:Q155").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
End SubSub Macro1()
'
' Macro1 Macro
'
'
Range("B4:M2000").ClearFormats

Range("B4:M2000").ClearContents


Sheets("Autoclave 1").Select
Range("A5:L30").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B3").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A31:L58").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B35").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A59:L81").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B65").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A82:L107").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B95").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A108:L135").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B125").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Autoclave 1").Select
Range("A136:L159").Select
Selection.Copy
Sheets("Comp.A1").Select
Range("B155").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("B3:Q3").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B35:Q35").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B65:Q65").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N65"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B95:Q95").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N95"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B125:Q125").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N125"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B155:Q155").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Add Key:= _
Range("N155"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=18
Range("B155:Q155").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("Comp.A1").AutoFilter.Sort.SortFields.Clear
End Sub


Disable adblock

This site is supported by ads and donations.
If you see this text you are blocking our ads.
Please consider a Donation to support the site.


Patropi
Colaborador
Colaborador
Mensagens: 47
Registrado em: Ter Jun 15, 2010 9:17 pm

Re: Macro copiar e colar com criterio

Mensagem por Patropi »

Bom dia

Sem anexar um arquivo Excel fica complicado para o pessoal tentar ajudar.

[]s


Responder