Excel VBA Macro

[MachoFlyer813]

Hello
I want to start out with saying am not familiar with VBA programming and have been struggling with this task. I have been trying to do some research and figure out what I need to do to accomplish my task. What I am trying to do is go into a folder and pull specific data out of some excel sheets and paste it onto a master excel sheet.

Some background, we autogenerate these forms to our customers and it shows their production rates for that day. I am looking at compiling a group of these reports to study some of the data from our systems.

I am looking to copy the information in cells C6:C20 (See info to be Copied) and paste it onto a mastersheet. All of the workbooks from the host files will have the sheet name of "3462UrbanSawDataReportCSV_2022-" and the destination sheet would just be "Sheet 1". The copied cells will always be C6:C20. I would like to paste the information like shown in the picture "Pasted Information" but if it has to be in the same orientation as the copied data I can make that work.

I would like to have it so when you start the macro you have to select a folder vs having a specific folder specified in the code.

On top of all of that I didn't know if there was a way to not have specify the host sheet name. I mentioned the sheet name above as "3462UrbanSawDataReportCSV_2022-" but that is only linked to our job number 3462. Each one of our systems has a different job number. I would be fine going in and modifying the code to change the sheet name for each time I ran this but if there was a way around that it would be great!

Any help I can get would be greatly appreciated. Let me know if I left out any prevalent information.

 

[Micron]

I know what you mean about vba. When I first started, in a conversation I asked how to raise a message box
After about 20 years, I might rate myself as a 4 or 5 out of 10 altogether (Access and Excel vba) but I'm much stronger at Access vba. Maybe some would say I'm being too generous to myself but I put that down to not doing it every day over that period. Anyway, point is if you know waht to ask you can Google and learn a lot but knowing what to ask is sometimes a limiting factor. When something works but you don't understand it, a forum is a great place to learn too. Not sure if what you're looking for here is guidance or completed code. I'm going with the former.

There are at least 3 ways I know of to open a file dialog. My preferred method is msoFileDialogFile(or Folder)Picker for which you need a reference to Office, I believe. Folder returns the folder path but you cannot see files in the view. File picker returns the complete file path so AFAIK you have to lop off the file name if you only want the folder but need to see file names. So you might start with researching that.

Once you choose the file, you'd open it using the path returned by the dialog. Might as well open it hidden. Then you'd copy your static range. Then you'd paste it into the target sheet. Here's where my lack of knowledge kicks in - I don't know how to transpose from columns to row(s) in vba but this is one result that I came up with in 1 minute:

VBA Transpose

We have seen the Transpose function in the Excel worksheet when we paste any data table to the worksheet. Transpose changes the position of rows and

www.wallstreetmojo.com

Last but not least, it's not clear if there will only be 1 header row and 1 value row in the target sheet. If data already existed, then what? Maybe the requirement would involve clearing the target sheet first. Or maybe new data is appended somewhere below? Or maybe you could have target sheet as a template and just transpose the data?

 

[MachoFlyer813]

There would only be one header for the master sheet. As for the values I would want the first sheet in row 2 and the second sheet in row 3 and so on for however many sheets there are in the folder.

I plan to always have a blank "Master" copy of this master sheet so that the rows where the data needs to go will always be empty when I run it.