Macor to extract data from files with different row counts

pbthatsme

New Member
Joined
Dec 7, 2004
Messages
7
Each month we receive several files from servicers. There are some files which have the same format, but a different number of row counts. I would like to create a macro so I am not repeating the same steps for each file, but we my limited experience I am not sure how to create the macro such that it can handle the differing row counts. I know if I create the macro by having Excel code my steps, that will not work for each spreadsheet because of the difference in row counts.

If some could advise how I can get past that either through examples or explaination I would appreciate it.

I am using Microsoft Excel 2000

Thanks
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
that shouldn't be a problem. If you do the macro record with one file, then it can be cleaned up and variables added to find the last row.
 
Upvote 0
Recorded macros can normally easily be adapted to be more dynamic.

In your case I think we would need to know some more information to give further help.

Are the files all in 1 directory?

Do you want to copy all the data from all the worksheets in all the workbooks?
 
Upvote 0
Thanks for the responses, I will do my best to describe the situation. But first, I will answer one of the questions. Yes the files are in the same directory.

Each month we receive several files, for example 20. Of those 20 files we can have a set of 5 in one format, another set of 5 in another format, etc. Using one set as an example, those files can have up to 30 fields, but we only need 10 specific fields of that 30. The other set of five can have 15 fields and we need 10 fields.

Going back to the subject of different formats, formats even includes different field names. So the first set of 5 may have a field we need called Ending Schedule Balance and the other set of 5 files can have that same field called EndSchBal, but in an entirely different location.

Also, each month, the most of the file names are different. Thus, my thought is there needs to be a drop-down list or something that identifies the format type, not taking the file name into consideration. So say Chase and WAMU send us the same file format, either the user can select Chase or WAMU knowing who the servicer was and the macro would then know if Chase or WAMU were chosen to use the same routine.

Basically, right now, I have to select, copy, and paste the fields I need from each file and put it into a different workbook and I have to do this for every file that we receive. I also take that information and append each new set of data to the data I just did a select, copy, paste to.

I hope this cleared things up a little.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top