I searched around for a while and found there wasn't exactly a solution for what I wanted. You can add to the below easily to loop through all sheets in a workbook, but I was working on a specific requirement of single sheets.
If you have a list of files you can work through to combine, these are the key points you would need to do the task though*:
'The following three strings need to be the full file path and the full excel source name to work including the extension.
'e.g.: sTemplateFile = "C:\Temp\MikesAwesomeTemplate.xls"
sTemplateFile = "" 'Used so formatting and base properties were as required for use. Not necessary if you are prepared to merge bk2 and bk3 directly.
sProdFile = ""
sTestFile = ""
Set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
Set bk1 = oXL.Workbooks.Open(sTemplateFile)
Set bk2 = oXL.Workbooks.Open(sProdFile, UpdateLinks:=False)
bk2.Worksheets(1).Name = "PROD"
Set bk3 = oXL.Workbooks.Open(sTestFile, UpdateLinks:=False)
bk3.Worksheets(1).Name = "TEST"
bk1.SaveAs Filename:=sSaveAsPath, _
Password:="", WriteResPassword:="", _
oXL.DisplayAlerts = True
Set oXL = Nothing
*I used the above code in Excel, not QTP. I have added the use of the oXL object for the purposes of QTP use, but if you remove it throughout you can use it directly from Excel.