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