Vba re arrange date into a second sheet

[ANTONIO1981]

HI

in the attached file NEW SITES & RENOVATIONS.xlsx - Google Drive


I have to re arrange the data

the target is to create sheet 2 based on the information in Sheet 1 column C ( take only data containing words "Renovation", "Disposal", "New sites" )

Sheet 2 row A is hardcoded so no code is required

how the code will be?

thanks in advance

AC

 

[ANTONIO1981]

I have this code but it doesn't work when my data instead of starting in A1 in sheet1 starts in P9 . how can in correct?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub test()
Sheets("sheet2").Cells(1).CurrentRegion.Offset(1).ClearContents
With Sheets("sheet1").Cells(1).CurrentRegion
.Parent.AutoFilterMode = False
.AutoFilter 3, Array("Renovation", "Disposal", "New sites"), 7
.Copy Sheets("sheet2").Cells(1)
.AutoFilter
End With
End Sub</code>