Modify recorded macro to Worksheet Activate

[guatelize]

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

[Bob Phillips]

What is the problem with it, what does it do/not do?

[guatelize]

I'm trying to run the macro on selecting Sheets("Data") with Private Sub Worksheet_Activate as shown in the attachment. When I select the "Data" sheet an error appears.

[Bob Phillips]

It's quite possible this doesn't now do what it is supposed to do, it needed so much tidying



Private Sub Worksheet_Activate()

Me.Cells.ClearContents
With Sheets("Raw Data").Cells
.AutoFilter Field:=14, Criteria1:="<>*00*"
.AutoFilter Field:=8, Criteria1:="15 - Road"
.SpecialCells(xlCellTypeVisible).Copy
End With
Me.Range("A1").Select
Me.Paste
Columns("A:A").Delete Shift:=xlToLeft
Columns("N:N").Cut Columns("E:E")
Columns("H:H").Cut Columns("F:F")
Columns("Q:Q").Cut Columns("D:D")
Columns("G:H").Delete Shift:=xlToLeft
Columns("I:P").Delete Shift:=xlToLeft
Range("I2").FormulaR1C1 = "=-RC[-2]"
Range("J2").FormulaR1C1 = "=-RC[-2]"
Range("I2:J2").Copy Range("I2:J2").Resize(Range("H2").End(xlDown).Row)
Range("I2:J2").Copy
Range("G2:H2").PasteSpecial Paste:=xlPasteValues
Columns("I:J").Delete
Columns("H:H").Cut Columns("G:G")

Sheets("Raw Data").ShowAllData
Application.CutCopyMode = False
End Sub

[guatelize]

Thanks, it worked perfectly. Super