
Originally Posted by
zookcruzr
I know the easiest option would be to post a sample, however to the get the results I want I would need to change the names and keep atleast 20 entries in each sheet. All while maintaining low percentage of duplicity. Too much work

.
…and even more work for those considering trying to answer your query.

Originally Posted by
zookcruzr
Whilst writing this I have decided to make a sample lol.
That's better!
A macro to be put in a standard code-module f the file you attached:
Code:
Sub blah()
SheetsToExclude = "SAMPLE PIVOT,Reports,Single Table,New Pivot"
Headers = Array("Date", "Event No", "Patient Name", "Offence or MH condition", "Drug Related?", "Alcohol Related? ", "Suburb", "Time at Scene (Record Minutes)", "Travel Time", "Hospital Waiting Time", "Return travel time", "Total Time", "Sec 22 Police vehicle Escort", "Sec 22 Ambulance with police Escort", "Sec 20 Ambuance with police Escort", "Section 20 Ambulance Escort", "Other")
Set SingleTableSheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
SingleTableSheet.Cells(1, "A").Resize(, UBound(Headers) + 1) = Headers
SingleTableSheet.Name = "Single Table"
DestRow = 2
For Each sht In ThisWorkbook.Worksheets
If InStr(SheetsToExclude, sht.Name) = 0 Then
With sht
'.Select
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
If lr > 1 Then
'process
.Cells(2, "A").Resize(lr - 1, 17).Copy SingleTableSheet.Cells(DestRow, 1)
DestRow = DestRow + lr - 1
End If
End With
End If
Next sht
End Sub
and an attached workbook with just a consolidated table made by the macro, then I added 2 pivot tables manually.
Bookmarks