I have a large lookup tables–one on each of twelve different worksheets in a workbook. Each of these worksheets represents financial information for a particular month. I have 30 different columns that represent salesmen [women] and 500 rows that represent products. I should mention that Column A and B contain a part number and item description respectively (i.e., Column C is the first salesperson column—the next 29 columns after column C are sales people). This workbook is close to 100Mg…so I want to generate the reports from a separate workbook as I am running into memory limitations on my PC.
A salesperson doesn’t sell all items. So I have many, many zeros–reflecting no sales of that item–in each salesman column. What I want to do is write a macro in the new separate workbook that creates a report by salesperson and lists only those items sold for the month. So if the sales person in column C sold only 10 out of the possible 500 items, the report would indicate the sale person and then have three columns of information: part number (column a), description (column 
, sales $ (column c)—only ten consective rows (no blank rows) since only 10 products were sold. Then a total for column C. I would then need this macro to do the same action on the next 29 columns in the “Jan” worksheet in the original 100mg workbook . Then each month the macro would access the correct worksheet (Feb., Mar, April, May, etc.) in the original workbook to generate the reports.
I don’t need a worksheet for each salesperson report…so the next salesperson report (for example, column d) can be a couple of rows after the previous sales person (for example, column C). I would need the a separate worksheet for each month to correspond with the twelve worksheets (months) in the original workbook.
I have played with some code and my “new” report has blank lines where there are no sales in the original workbook. I can not discern how to “read” the value in the original workbook and if its zero to move to the next row while still keeping the same row location in the “new” report waiting to write the next product that does have a value. How do you “read” from one worksheet, and if the criteria test “fails” move down to the next row while keeping the report row in the worksheet the same waiting to write the next value when the criteria test “passes”? I have also had to copy the “jan” worksheet from the 100Mg workbook to a new workbook in order to start this report process as the original workbook is so large and eats up all my memory. I have seen in the lounge several posts that indicate that I can get an “add-on” that will permit me to pull information from a closed workbook. Is this something I need for this project or is there something else I am missing?
I hope I have explained this sufficiently….thanks for your patience and reading through it.
