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