Macro for analyzing data and returning lines that meet certain criteria

**Tdunlap** · 02-09-2012, 02:01 PM

Hello, everyone! I am new to this forum and new to VBA. Thank you in advance for your help!

I receive an excel file daily that contains submitted quotes for projects to a customer. It includes all revisions of the quotes, the approval status, the revision, and total for a project. The list contains several thousand projects, and each revison receives a new quote #

For Example:

Please Login or Register  to view this content.

How would I write a Macro to pull in the Total for the latest Approved Revision for a Project, by line, to a another worksheet? Or remove all lines that do not meet this criteria from the recieved work sheet?

Using the above example, I would want this line:

Please Login or Register  to view this content.

**smuzoen** · 02-10-2012, 08:56 AM

You could use the following - this will allow you to browse to the file to import and then it will import only Approved quotes into the workbook. See attached workbook.

Please Login or Register  to view this content.

**Tdunlap** · 02-10-2012, 11:19 AM

Thanks for the reply!

However, I am receiving "Error 9 - Subscript out of range" When ever I try to run the Macro.

Also, is it required that I keep CSV as the only file type, because the data I receive is already in xlsx. Could converting my data to CSV from xlsx before the import attempt be the issue?

Changed error handler line to

Please Login or Register  to view this content.

then used debug and it took me to this line

Please Login or Register  to view this content.

**smuzoen** · 02-10-2012, 08:32 PM

Can you upload a sample file that you import - I assumed it was a csv file - that is an easy fix. Did you copy my code into your own workbook - if so you need to change the line

Please Login or Register  to view this content.

Could you upload also a sample workbook that the data gets imported into - subscript out of range just means it cannot find the sheet. What sheet does the data get imported into? Upload a sample file that you import and a sample workbook that you want the data imported into. It is just a matter of referencing the sheet and workbook correctly. Sample workbooks with no sensitive data makes it easier to give you an accurate answer.