guatelize
09-17-2008, 02:33 AM
Hello
I've a problem with the attached recorded macro. I need to activate it when the sheet is selected.
How can I modify it ? Is it also possible to simplify it ?
Thanks for your help
Private Sub Worksheet_Activate()
Sheets("Data").Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("Raw Data").Select
Selection.AutoFilter Field:=14, Criteria1:="<>*00*", Operator:=xlAnd
Selection.AutoFilter Field:=8, Criteria1:="15 - Road"
Cells.Select
Selection.Copy
Sheets("Data").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Columns("H:H").Select
Selection.Cut
Columns("F:F").Select
ActiveSheet.Paste
Columns("Q:Q").Select
Selection.Cut
Columns("D:D").Select
ActiveSheet.Paste
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("I:P").Select
Selection.Delete Shift:=xlToLeft
Range("I2").Select
ActiveCell.FormulaR1C1 = "=-RC[-2]"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=-RC[-2]"
Range("I2:J2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("G2:H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("I:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Sheets("Raw Data").Select
ActiveSheet.ShowAllData
Sheet1.Select
End Sub
I've a problem with the attached recorded macro. I need to activate it when the sheet is selected.
How can I modify it ? Is it also possible to simplify it ?
Thanks for your help
Private Sub Worksheet_Activate()
Sheets("Data").Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("Raw Data").Select
Selection.AutoFilter Field:=14, Criteria1:="<>*00*", Operator:=xlAnd
Selection.AutoFilter Field:=8, Criteria1:="15 - Road"
Cells.Select
Selection.Copy
Sheets("Data").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Columns("H:H").Select
Selection.Cut
Columns("F:F").Select
ActiveSheet.Paste
Columns("Q:Q").Select
Selection.Cut
Columns("D:D").Select
ActiveSheet.Paste
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("I:P").Select
Selection.Delete Shift:=xlToLeft
Range("I2").Select
ActiveCell.FormulaR1C1 = "=-RC[-2]"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=-RC[-2]"
Range("I2:J2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("G2:H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("I:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Sheets("Raw Data").Select
ActiveSheet.ShowAllData
Sheet1.Select
End Sub