Copy & Paste with Transpose from MANY files to ONE

Hi All! This is my first post, I recently started using Excel VBA and was hoping to get some help!

I need to copy content from multiple files Range("E7:E35") (column form) and transpose them into a "Master" worksheet in row format (one after the other).

I found code to help select a folder and go through the files; however, I keep getting a runtime error 91 "Object variable or With block variable not set"
(sorry can't link source because I'm a new user, but see code below)


I tried changing the reference in "Tools" to "Microsoft Office 16.0 Object Library" ONLY, but I was not able to uncheck or reprioritize "VBA" or "Microsoft Excel 16.0 Object Library", which are ahead of it.

Sub File_Loop_Example()
    'Excel VBA code to loop through files in a folder with Excel VBA

    Dim MyFolder As String, MyFile As String

    'Opens a file dialog box for user to select a folder

    With Application.FileDialog(msoFileDialogFolderPicker)
     ' ERROR 91 SHOWS UP HERE
       .AllowMultiSelect = False
       .Show
   
    End With

    'stops screen updating, calculations, events, and statsu bar updates to help code run faster
    'you'll be opening and closing many files so this will prevent your screen from displaying that

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'This section will loop through and open each file in the folder you selected
    'and then close that file before opening the next file

    MyFile = Dir(MyFolder & "\", vbReadOnly)

    Do While MyFile <> ""
        DoEvents
        On Error GoTo 0
        Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
        ''''''''''''ENTER YOUR CODE HERE COPY AND PASTING AND TRANSPOSING
        MsgBox MyFile
0
        Workbooks(MyFile).Close SaveChanges:=False
        MyFile = Dir
    Loop

    'turns settings back on that you turned off before looping folders

    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationManual

    End Sub

Any help or comments would be appreciated!

Thank you so much

Originally Posted by Mumps1

What is the name of the source sheet in each file?

Name of source sheet is "Sheet1"

Originally Posted by Mumps1

What is the extension of each source file (xlsx, xlsm)?

Extension is .xl**
(Varies from file to file)

Originally Posted by Mumps1

Are the source files the only files in the folder that contains them?

Yes, the source files are the only files in the folder.
I'd like to be able to select one at a time, because they are grouped by different indicators, but are all formatted the same way in terms of what range will be selected.

Originally Posted by Mumps1

Do you want to select the folder or do you want the macro to automatically open the files in the source folder?

I want the macro to automatically open the files in the selected folder

Originally Posted by Mumps1

If you want the macro to do the work, what is the full path to the folder containing the source files?

/Users/name/Desktop/Coded/Duplicates/Coding sheets

I have a bunch of files in a multiple folders, then the workbook that the macro is in acts as my "Master" sheet where I want to compile all the data.
I'd like to be able to run the code separately for each folder, that's why I was initially looking at the folderpicker command.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim flder As FileDialog, FileName As String, FileChosen As Integer
    Dim desWS As Worksheet, wkbSource As Workbook
    Set desWS = ThisWorkbook.Sheets("Master")
    Set flder = Application.FileDialog(msoFileDialogFilePicker)

    flder.Filters.Clear 'HERE I hit an error 91
    flder.Filters.Add "Excel Files", "*.xl*"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set wkbSource = Workbooks.Open(FileName)
    Sheets("Sheet1").Range("E7:E35").Copy
    desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
    wkbSource.Close savechanges:=False
    Application.ScreenUpdating = True
End Sub

Is there possibly anything wrong in my settings?

I just read that FileDialog is not available for Macs, I'm going to try and run this on a PC and will report back soon

Now that I'm working on windows, I am able to use the folder picker.
I'm getting a run-time error 1004 Application-defined or object defined error.

Sub Macro2()
Dim y As Workbook
Dim desWS As Worksheet
Dim Lastrow As Integer
Dim MyFolder As String
Dim MyFile As String



Set y = ThisWorkbook
Set desWS = ThisWorkbook.Sheets("Master")

' Clearing file for content
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row

Range("A2:AC2" & Lastrow).Clear






' Set MyFolder as selected folder
 With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
      
        MyFolder = .SelectedItems(1)
        Err.Clear
End With


Application.ScreenUpdating = False


MyFile = Dir(MyFolder & "\", vbReadOnly)

Do While MyFile <> ""

Workbooks.Open FileName:=MyFolder & "\" & MyFile, UpdateLinks:=False
Sheets("Sheet1").Range("E7:E35").Copy

' Run-Time Error 1004 HERE
desWS.Cells(desWS.Rows.Count, "1").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True

Workbooks(MyFile).Close savechanges:=False
MyFile = Dir
Loop

Application.ScreenUpdating = True



End Sub

It worked, thank you!