VBA code to copy/paste rows conditionally based on cell value in column

Hello,

I would like some help figuring out how to do the following:

In one sheet I have 5 columns: Location, Item, Description, Qty, Unit Price.

Sheet 1.png

The objective is to comb through the Location column and copy the Location value in a new sheet and then copy all the rows that have that same Location value and past them underneath.

Sheet 2.png

Any help will be appreciated! Thanks!

Hey Kev, thanks for the input however a pivot table won't do the job 100% since I'm intending on using the code in a template.

Does this help?

Sub thedefense()
Dim textrange As Range, i As Long, y, ws As Worksheet, wsN As Worksheet
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
Set ws = ActiveSheet
Sheets.Add.Name = "New Sheet"
Set wsN = Sheets("New Sheet")
wsN.Cells(1, 1) = "Item"
wsN.Cells(1, 2) = "Description"
wsN.Cells(1, 3) = "Qty"
wsN.Cells(1, 4) = "Unit Price"
With ws
ReDim y(2 To .Range("A" & Rows.Count).End(3).row)
For i = UBound(y) To LBound(y) Step -1
    If .Cells(i, "A") <> .Cells(i + 1, "A") Then .Rows(i + 1).Insert
Next i
For Each textrange In .Range("A2:A" & .Range("A" & Rows.Count).End(3).row).SpecialCells(2, 2).Areas
    addr = textrange.Address(False, False)
    wsN.Cells(Rows.Count, 2).End(3)(2) = .Range(addr).Cells(1, 1)
    .Range(addr).Offset(, 1).Resize(, 4).Copy wsN.Range("A" & wsN.Range("B" & Rows.Count).End(3)(2).row)
Next textrange
.Range("A2:A" & .Range("A" & Rows.Count).End(3).row).SpecialCells(4).EntireRow.Delete
End With
wsN.Cells.Columns.AutoFit
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

Thanks John. This is perfect.

@ thedefense - You're welcome. Glad to help out and thanks for the feedback and the reps.

@ kev_ - The purpose of array y is to add speed to the Loop for the variable i.

@ kev - Sorry for the late reply, been busy. Yes looping large data sets using an array tends to run faster then using a normal loop.
Though there many other resources too support this here is one link.

https://stackoverflow.com/questions/...s-in-vba-excel. Hope that helps.