line item extractor

Ok, so here is the deal. I have multiple excel files with 20-30 sheets per file. These files are full of data, account numbers etc etc. I have been trying to build a macro that can crawl through every sheet and extract line item. For example, in the attached worksheet, if i want to extract the rows for account number 251122812 from all sheets, and put it in a new file or maybe a sheet within the main file, how can i do it? Please help me out, there are thousands of line items that need to be extracted, and its simply a tedious work to do it manually....

Any help will be appreciated and remembered

We need to clarify what you're really after.

1) Do you want to create new worksheet for all items matching single account number?

2) If yes, is this worksheet in a new master workbook or inside one of your many workbooks?

3) Are we consolidating the info in all sheets in ONE workbook (already open) or do you want to sift through an entire folder on your hard drive and pull in all the data for a single account number?

4) If sifting through an entire folder, your data does not include dates or any identifying information. Would you need to add the workbook name and/or sheet name each bit of data came from, or no?

===============
I would think possibly something like this:

1) In a Master workbook, prompt user to enter an account number
2) If a worksheet already exists for that account number, clear the sheet and get ready to enter new data, if not then create this new sheet
3) Prompt user to select a folder to sift through all files
4) After folder is selected open each file one at a time
5) In each file opened, cycle through ALL sheets and filter for data in column A matching the account number, copy it to the Master-Acct # sheet adding workbook names and sheet names to each row of copied data.

Is that about right?

Got a sample workbook with the exact layout and a good set of sample data?

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Last question, will the workbook to search already be open? I envision the beginning of the macro prompting you to enter an Account number, then prompting you to select a workbook from the hard drive. The macro opens the wb, pulls all the data, closes that workbook, then saves this new wb and leaves it open for you to review.

Put this into an empty workbook to run from, it won't be in the workbook created, nor needs to be in the workbooks being searched.

Please Login or Register  to view this content.

If you already have the workbook open that you want to search/consolidate, then click on the account number and run the macro. Whatever the Activecell is will be used to offer you an account number verification, just press ENTER to accept it and YES to confirm this is the workbook you want to search.

If this ISN'T the workbook, just enter the correct Account Number and then say NO to the workbook question, it will let you select another workbook.

The workbook created will be saved with the account number and the current date.


This is based on the macro ConsolidateSheets from my site here:

Sheets to 1 Sheet

How/Where to install the macro:

1. Open a new Empty workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use.

===========
How to use the macro:

1) With this workbook open, enter an account number into a cell and click it to make sure it is the active cell
2) Press Alt-F8 and select ConsolidateInfoFromSheets from the macro list.
3) Confirm this the account number you want, or enter a new one in the popup and press OK
4) If the current active workbook is not the workbook to search, click NO and select the workbook from your harddrive

THe script is awesome. I am so glad i am getting help from you. there is a problem though. When i enter accoutn number and press 'OK', it asks me to add workbooks, i search for workbook and then press OK. i get error code 91.


Runtime error 91

After Debugging:This section is highlighted in Yellow

Please Login or Register  to view this content.

Please edit your post to add CODE tags.

In my signature is an example of the

Please Login or Register  to view this content.

[/noparse] tags you need to put around the code you posted above. EDIT your prior post and add them. The result will look like my post #8.

The hidden sheet SQL-SETTINGS caused a hiccup. I corrected the code above in post #8 (added a ws reference to help overcome the problem. I tested it an it's working now. Try it again.

And when you debug, which line of code is highlighted?

That line of code is the macro trying to spot the "Account Number" string on the currently evaluated sheet. It should simply fail and move on, which it does on the visible sheets. I still think you're getting hung up on the hidden worksheet, though it isn't doing that for me.

Maybe add this:

Please Login or Register  to view this content.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

There's a scales icon in my posts you can use for a reputation feedback.

And you can visit my site for more code excerpts. This macro for you was based on code from there, the link is in my signature.

Can you post the full macro again so we can see it in it's current complexity?

Odd, that looks like the original macro. I don't see any changes, including the one you got from post #19.

The macro is collecting data going through the worksheets in the workbook one at a time. It's simply using the order of sheets that you have.

Try this:

Please Login or Register  to view this content.

Account numbers cannot start with zero without first converting those cells to text....which goes against the idea of an account "number", eh?

Convert your account numbers to actual numbers, then use cell formatting to "display" leading zeros if you really feel you need to see them. Then the macro should work fine as designed.