Hello from Steved

Question with the below is it possible to have the same format in other
sheets that it copies to please.

And lastly can I copy the heading from Reporting Sheet to the other sheets
the heading is on row 5, so I would liki it to be placed in row 1 off the
copied sheets please. Thankyou.

Sub test4()
Dim rng As Range
Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "Reporting Sheet" Then
Set rng = FilterData(WS.Name)
If Not rng Is Nothing Then
rng.Copy WS.Range("A2")
End If
End If
Next WS
End Sub
Private Function FilterData(sCity As String) As Range
Dim cRows As Long
Range("A1").EntireRow.Insert
Range("A1").FormulaR1C1 = "temp"
cRows = Cells(Rows.Count, "A").End(xlUp).Row
With Columns("A:A")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=sCity
End With
Set FilterData = Range("A2:A" &
cRows).SpecialCells(xlCellTypeVisible).EntireRow
Rows("1:1").Delete Shift:=xlUp
End Function