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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

It worked, thank you!