transpose across workbooks

Thanks I'll let you know how it goes when I have a chance to test it this coming Monday. Managed to get a hardcoded version to work at least partway, if this macro puts the info in the mastersheet tab I'm currently on, that would certainly cut down on the need to replicate the same macro and alter the cell & name references.

Will edit this post with the result, running it as-is got an error at this following line, will explore later and will share my inelegant (but working )solution when I have the chance.

    For Each aSheet In allSheets
        thisSheet = aSheet.Name
        Sheets(thisSheet).Select
        r = Range("A65536").End(xlUp).Row
        Range("A7:B" & r).Select
        Selection.Copy
        Windows("MasterSheet.xlsx").Activate
        Cells(1, msR).Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        
        msR = msR + r - 6
        Windows(inputBook).Activate
    Next

Yep that worked, although I guess my master sheet example was not clear, as I have many many workbooks to merge, each workbook of the same type as it gets added in needs to go in the row below the prvious one so as not to overwrite the data. So the first tab in the mastersheet will eventually have some 30+data rows with the top row being the headings filled by all the type1 workbooks.
Let me throw up my rather inelegant solution that needs one macro for each worokbook type, i.e. it's a hardcoded solution.

Sub Merger()
'
'Macro to merge the tracking spreadsheets into one tab in a master sheet'
'Applies only to the Type1 sheets'
Dim wbk As Workbook
Dim Filename As String
Dim Path As String

Path = "C:\Work\spreadsheetdata\Type1\"   
Filename = Dir(Path & "*.xls*")


'--------------------------------------------
'OPEN EXCEL FILES
   Dim no As Integer
   'no is the variable representing row number'
   no = 2


 Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)
Application.ScreenUpdating = False

    Sheets("Tab1").Select
    Range("A8:A23").Select
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Sheets("Type1").Select
    Range(Cells(no, 1), Cells(no, 16)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab3").Select
    Range("A7:A8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 17), Cells(no, 18)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab4").Select
    Range("A7:A8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 19), Cells(no, 20)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab5").Select
    Range("A7:A8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 21), Cells(no, 22)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab6").Select
    Range("A7:A8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 23), Cells(no, 24)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab7").Select
    Range("A7:A10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 25), Cells(no, 28)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab8").Select
    Range("A7:A10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 29), Cells(no, 33)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab9").Select
    Range("A7:A8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 33), Cells(no, 34)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows(Filename).Activate
    Sheets("Tab10").Select
    Range("A7:A9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestMasterSpreadsheet.xlsx").Activate
    Range(Cells(no, 35), Cells(no, 37)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
    wbk.Close True
    Filename = Dir
    
    no = no + 1
Loop

Application.ScreenUpdating = True

End Sub

I realise I should have chosen a shorter example as not all of them have this many tabs :p
The things highlighted in red will always be different and where a line says "Range(Cells(no, 21), Cells(no, 22)).Select" The numbers represent the column number range for the copied items to be pasted into.

This code opens up every file in a folder (coded at the very top), and copy pastes the provided ranges into the mastersheet, then closes the input workbook, adds 1 to the row number, and then moves onto the next. The Masterspreadsheet is open while this macro runs.

Welcome any feed back on how to tidy this up or jsut have ONE macro and select the input location.