Need macro to locate non-zero, non-header column entries then copy to other sheet

Hello all,

I'm soooo close to finishing estimating spreadsheet for which this group has been talented enough to solve two prior problems.

My current and hopefully final issue is illustrated in the attached "mock-up" - which is an abbreviated version of much larger actual file - and described as follows:

On 1st worksheet "Detailed Estimate", a series of numbered tasks are available for the user to enter anticipated subcontractor costs at Column F. Column F also repeats the text "Subcontractor" as a header with each task. So entries in Column F can be predicted to be either "Subcontractor", null, or some dollar value. If a dollar value is entered, there will be an associated description in Column G.

On 2nd worksheet "Summary Sheet", I'd like to automatically search "Detailed Estimate" column F for each dollar value entry, then retrieve that value - along with its associated description from column G - into the Summary Sheet. Note that the entries are transposed from 1st sheet to 2nd sheet...but if this is a hassle the order can be kept same.

Retrieving also the task identifier would be great, but this is not essential and is of much lesser importance than just retrieving the dollar values and description.

The macro will need to add rows as needed to "Summary Sheet" in order to fit the copied data; the number of entries will depend on the user and is theoretically limitless...though will be between 5 and 20 typically.

Thank you very much for your responses.

-Steve

Jerry,

Thank you very much for taking the time. I'm not a VBA programmer so it took me a bit of thinking to follow your code's approach (was also helpful to step through the code using the debugger). I think I understand most of it and it's a very creative approach.

I attached this to my command button and it works just fine. There is a bit of a quirk if you press the button, then press it again. I changed the .Clear early in the code to .ClearContents and that seems to work a bit better. I also changed the color index formatting to just a white background. See attached "mockup 3rev" for the spreadsheet invoking the code.

If you wouldn't mind taking to the next level, there will be some issues when I translate this to my "real" spreadsheet:

(1) "Detailed Spreadsheet" column H is used in the code as a place for temporary formulas; in reality there are many more columns..."H" is really out at BM for example...and user is free to add/delete columns. I believe this can be addressed by using a far out column such as HH instead of H...unless there is some way to make "H" dynamic?

(2) As shown on attached "mockup 3rev mod", the subcontract totals will actually fill a center section of rows that will vary (right now using rows 12-28)...so actual start position would float based on edits made to rows above by user. Currently, the code is hard-coded to start at A4...and also assumes no data below -> any way to float the start and preserve the bottom?

What you've provided already has been a great help. Anything further also much appreciated.

Regards,

Steve

Jerry,

I realize I tied your hands with the oversimplified spreadsheet - sorry about that. There was certain information I couldn't make public. I've attached a detailed version that is far closer to the actual working version.

Regarding item (1): Column BK is the first blank column in the detailed mockup...but still subject to change...but no more than 10 or 20 columns would reasonably be added beyond current layout, so something like DD would work as the temporary formula workspace. The "Subcontractor" and "Description" fields being retrieved - which were at F & G in the simplified mockup - are out at BH and BI in the detailed mockup...but subject to float left or right depending on column insertions or deletions. Columns A-E are set content; columns (currently at) AS and beyond are set content; but user is free to add or delete columns between (each column corresponds to a staff person name).

The "Subtotal Hours" phrase that you used as a search term reads "Subtotal Hours (identify task from pulldown)" in this version...but is at same column D as before and still adjacent to a field that the Task ID can be drawn from...but now there's a variable distance between this marker and the "Subcontractor" and "Description" fields way out to right. Would need some other way to march focus over to there.

(2): On the Summary Sheet, note the green highlighted section -> that would be the target location to populate from the macro. It can be located by the heading "Subcontractor Costs", but that heading location would change if user adds or deletes rows above. Right now, green highlights occupy 8 rows...but upon macro run the goal would be to only use the necessary number of rows, by adding or deleting as required. The totaling (Project Total) still needs to happen below, which is what I meant by "maintain the bottom", i.e. don't overwrite info below the green fields upon macro run.

-Steve