Consolidating multiple sheets

[johnnypol]

HI All

I am sure this question has been asked a thousand times before but i can't seem to find a solution ideally a vba.
What i have are multiple sheets of orders that i am trying to consolidate into a master sheet.
Basically i have a list of lets say 9 stores and they place orders for a range of elements i want to create a one consolidate picking list.
I have attached the file an example. This takes me about 50% of my working time as currently i copy and paste and then use vlookups.
Please help

Many Thanks Consolidate sheets.xlsx

[JOHN H. DAVIS]

Not sure if this helps?

Sub johnnypol()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Master" Or ws.Name <> "Desired outcome" Then
        ws.Range("A1:AB15").Copy Sheets("Master").Range("A" & Rows.count).End(3)(2)
    End If
Next ws
End Sub

[johnnypol]

Not sure if this helps?

Sub johnnypol()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Master" Or ws.Name <> "Desired outcome" Then
        ws.Range("A1:AB15").Copy Sheets("Master").Range("A" & Rows.count).End(3)(2)
    End If
Next ws
End Sub

I Get a subscript out of range error when i run this VBA so not sure if this could help

[johnnypol]

I Get a subscript out of range error when i run this VBA so not sure if this could help

I found out why it was giving me the error the sheet Master had an additional space at the end but this doesn't do what I need it just pasted the content of all sheets int the master 1 under another.

[JOHN H. DAVIS]

What do you want too happen?

[johnnypol]

What do you want too happen?

I want it to do the following in the master sheet starting from column F to paste the cells from the order 1, 2 and 3 sheets from rows 1 to 5 and columns F to wherever the data ends next to each other (the master sheet would end up with 23 columns starting from F and then in rows 7 to 15 the values would be returned in the correct column and row. Basiccaly it is like merging 3 orders together where certain receipients have several items being delivered. Basically i want to consolidate the 3 sheets into 1 so they end up looking like the "Desired outcome" sheet.