Copy multiple columns to new sheet ignoring blank rows?

[JumboCactuar]

Hi,
i need to copy all rows of data of Columns A,O,P where column A isn't blank (starting at row 10) to a new sheet
and repeat this on sheets1-5

sheet2 data merged underneath sheet1 data etc..

i have asked similar before but cant find the thread - instead of looping i think its possible with union join ?

Output should be 3 columns on sheet6 without any spaces

This is to prepare data for an SQL insert where I need to remove blank rows

any help appreciated

 

[JumboCactuar]

sorry - also need a 4th column filled with a cell from each sheet
Cell H1 has the sheet name of each

 

[Fluff]

How about

Sub JumboCactur()
   Dim i As Long
   Dim Ws As Worksheet
   
   Set Ws = Sheets.Add(, Sheets(Sheets.count))
   For i = 1 To Sheets.count - 1
      With Sheets(i)
         With .Range("A10", .Range("A" & Rows.count).End(xlUp))
            Intersect(.SpecialCells(xlConstants).EntireRow, .Parent.Range("A:A,O:P")).Copy Ws.Range("A" & Rows.count).End(xlUp).Offset(1)
            Ws.Range(Ws.Range("D" & Rows.count).End(xlUp).Offset(1), Ws.Range("A" & Rows.count).End(xlUp).Offset(, 3)).Value = .Range("H1").Value
         End With
      End With
   Next i
End Sub