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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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?
 

pbthatsme

New Member
Joined
Dec 7, 2004
Messages
7
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.
 

Forum statistics

Threads
1,136,354
Messages
5,675,294
Members
419,559
Latest member
BraytonM

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
Top