PDA

View Full Version : Logic to pull directory, two tabs and transpose data



amajor99
04-03-2018, 12:24 PM
Hi all,

Would anyone be able to help me with a VBA code?

Quick overview, I need to go into each "firm name" excel file within a folder and pull out data from the flat fee tab and the phase tier tab. Some data sets need to be transposed and some do not. I would like to pull the "Flat fee data into the flat fee tab on the input file, and the phase tier data into the phase tier tab on the input file) When this is done for one firm, I would like the file to close and then open the next firm and do the same thing.


1. Logic to pull several excel files from a specific folder/directory extract data and then close and move to the next. Let's call the folder/directory "Wisconsin" so I can easily spot and change it to the actual directory in the code.

2. Logic to open file named "Firm A" (which sits in the folder Wisconsin) and go into the tab named "Fixed Fee Pricing:hi:" and pull in the Firm name in cell B2 (return this firm name to the input file in the flat fee tab cell C1).

3.Logic to pull the "Fixed Fee Pricing" data from A8:A79 and return it to input tab "Flat Fee" A2 and going down.

4.Logic to pull the "Fixed Fee Pricing" data from D4 to W4 and return it to input tab "Flat Fee" B2 and going down.

5. Logic to pull the "Fixed Fee Pricing" data from D8:W92 and match up by column A and row 4 (so matching it by county/symbol) and then transpose it and return to input tab C2 and down.

so basically I want to lookup the county state, the symbol type and the fee and return it in the three columns in the input file by firm name (firm names to populate C1,D1,E1, etc.... ) with the pricing, county state and symbol going down.

Same thing for the Phase tier tab.


Then I want Firm A to close, and Firm B to open and populate all of the data in D1 going down. - Only the price though, since the county state and input file will have already been populated.





I am attaching the "Wisconsin" file that I would like to have the macro in, and then the Firm A and Firm B.

Thank you for any help. I am totally lost.

SamT
04-04-2018, 03:51 PM
Moderator Bump