Copy Rows with values greater than 0 from one sheet to another autmomatically

[larrysdime]

Hello All,

I have Two sheets involved in this scenario. The one where the rows of information exist is named "Duculator" the sheet where I want to copy the information to is named "Estimate Sheet".

I am trying to get certain cells in certain rows to automatically copy from the Duculator Sheet to the Estimate sheet when they have a value (Quantity) greater than 0. My thinking is that this may involve code which I have no understanding of and could really use some help here.

More specifically here is what I am trying to do:

Duculator Worksheet - Column A Is QTY (quantity) Column B is Size ( Not relevant to the information I want to copy to the other sheet) Column C is Description.

When any row has a value greater than 0 I want to copy the Qty and Description to the Estimate sheet starting at Cell B69 (Qty) and C69 Description. Of course if there are multiple rows on the Duculator that have qty's greater than 0 they would copy into B70 & C70, B71 & C71 etc.

I hope I have explained this well enough for someone to jump in and give me a hand.

Looking forward to working with others to improve my abilities.

Thanks,
Larry

[alansidman]

this code assumes you have data in cell B68

Please Login or Register  to view this content.

[larrysdime]

Hi Alan,

Thanks so much for your help. I may need to trouble you again. I am a complete idiot when it comes to code. How do I copy the code and where do I paste it in my workbook to make it work? May knowledge of code is next to none....lol

Thanks again.

[alansidman]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[larrysdime]

Hi Alan,

I'm not sure if my last reply and attached spreadsheet went through. Basically the code didn't seem to work. If I can attach the file you will notice on Cell B74 what the code produced on the estimate sheet. If you look above it at cell B69 and C69 You can see where I was trying to write some IFCOUNT Formulas to auto populate the Qty great than 0 in Column A and corresponding Description in Column C of the Duculator tab. (That isn't working well since my formula return a different description than the one it should have produced)

That said I was trying to find a way to get at Least 5 Lines starting at B69 and C69 of the estimate Sheet to auto populate with Values greater than 0 from Column A (QTY) and Column C (Description) of the Duculator Tab.

Basically I am trying to speed up data Entry for the estimator. I am trying to automatically bring the items over from the Duculator tab to the estimate sheet any time the estimator enters a value next to a duct service. This would save them time from having to filter the duculator sheet copy and paste back to the estimate sheet. The estimator may choose as many as 5 Duct items that are in the list. I don't envision a need for more than that.

Hopefully you'll see the attachment and thanks again for helping me with this.

[larrysdime]

Perfect HVAC Estimate2.zip

Hopefully I have attached the file this time.

[sktneer]

Please find the attached sheet and click on the yellow button on the Estimate Sheet to run the code and see if you get the desired output.

[larrysdime]

That certainly did the trick with the exception of a couple of things that I hope we can fix.

When it copied the items to the estimate sheet it had two impacts that I hope we can Correct. First it changed the font that was set in the desired paste area from Arial size 8 to Arial size 12. When I hit the update button it also changed the width of the column. I need to columns to stay fixed at a column width of 3. Each Column must remain at 3 in order for the form to fit on one page. When I manually re-adjusted the column widths to 3 the Description cell was cut off and unable to read the entire description.

Is it possible to fix those two things and this will work great?

I also have another request? Further up in the Estimate Worksheet I am trying to accomplish a similar action. Starting at line 27 thru 66 I am also trying to do a similar import of quantities greater than zero for the Following Columns:

QTY
Manufacturer
Model
Description
Cost

This information would be coming from the "equipment" tab where items have quantities greater than 1.

This is fantastic stuff and I hope we can make some modification and expand this same process to the equipment selection process as well.

Thanks again
Larry

[larrysdime]

Perfect HVAC Estimate3.zip

Here is what it looks like. I seem to have a hard time getting the attachment option right.

Thanks,

[sktneer]

See if the values from the Duculator sheet are being copied to Estimate sheet correctly.
For equipment sheet requirement, you have blank quantities column B, should I populate it some random quantities and is it >1 or >0 ?
Moreover you want values form these two tabs on the Estimate tab at once or you want to have two codes which you can run accordingly?

[larrysdime]

Perfect HVAC Estimate4.zip

It worked fantastically. I reattached version 4 with a few notes on it and some edit request. The data on the duculator side posted
great. However I was wonder if it could be pasted without affecting the borders as I have illustrated in #4?

Also on the equipment selection portion I also made some notes.

1. I think it should be a separate Macro to up date the equipment selected.
2. My mistake. The items to copy should be items with values greater than 0.
3. Feel free to add test quantities on the equipment sheet.

Amazing stuff. Just when I thought I really understood excel; I have quickly learned that it has even greater capabilities.

Awesome Job and Thanks so much!!!

Larry

[sktneer]

Hi Larry,

I will not be available for next 2 hours or so. Will definitely have a look at the workbook you attached in the last post and try to provide you a code to achieve the task.

[larrysdime]

Thanks Much. Its getting late Here as well so I will check back in the morning.

Just voted you the best and will be rooting for ya!!

[mridul2cool]

thanks Sktneer......I found this very helpful. Its great to be here.

[sktneer]

@mridul2cool
You're welcome. Glad that solution provided helped you too.

[sktneer]

Hi Larry,
Please find the attached workbook. The workbook contains two codes now which are assigned to two different buttons which can be clicked to run the codes whenever required.
Other than the code, you can apply the formatting as per your need.
I have also removed the conditional formatting for 0 values cells to have white font color, rather I have applied a custom formatting to the formula cells to hide 0 from these cells which I think is your original requirement.

[larrysdime]

Good Morning,

I made a few other changes to the File we were working on and have named it #5 so we are working on the same file.

I made some notes on the estimate page. I was wondering if it is possible to paste the data on the estimate sheet in Arial size 8 without affecting any of the existing borders? This would apply to both the equipment selection and the Duct selection. The only difference would be the quantities would be in bold to help them stand out. Here are the other parameters:

1. Copy paste data from Equipment sheet to estimate sheet starting at cell B27 that have quantities greater than Zero.
2. Run as a separate Macro with its own command button as illustrated in the attached.
3. Copy the following Cells (Fields) for the rows with quantities greater than 0.
QTY
Manufacturer
Model
Description
Cost

Here is the version 5 I mentioned above. Perfect HVAC Estimate5.zip

I hope you have a great day and look forward to hearing from you. This is really going to help our new company immensely.

Thanks Again,
Larry

[sktneer]

What is wrong in the file attached in post#16? This does exactly what you are trying to achieve.

[larrysdime]

Hi sktneer,

I apologize. I must not have been logged in properly when I sent my last email and hadn't seen your update. I have also been away from the computer all day as well.

I did just look at it and I want to say this is great stuff. Yes it does exactly what I wanted it to do and will help our team immensely. There are still a few border and heading issues that are happening. Is it possible to fix them? If not the team will have to go back and fix borders and section headings before printing and or sharing the documents. This of course would be counter productive to what we are trying to achieve.

Here is what I am seeing. When you press the update equipment button the following things occur.

1. Lines V27 thru V35 have dotted lines under the cell and to the right of the cell.
2. Solid Borders disappear on lines 67 & 68 thru 73 as well as Line 75.

Here is what happens when you hit the update Duct control.
1. It repairs some of the lines damage by the update equipment, but it wipes out the Border and the Section Heading "Subcontractors & permits"

That last section was not in the earlier version, but I decided to add it under the duct section since it is next. Basically the second page will be filled out totally when the form is totally done.

So I guess what I need it to do is not affect any existing borders on either page 1 or 2 of the estimate sheet. I have attached # 6 which is blank. You might want to save it first before hitting any command buttons. That way you will always beable to go back and see the borders and Headings as I have them now.

This is definitely the last piece of the puzzle and I can't begin to tell you how much I appreciate your help. This is really nice and hope that we can make these last adjustments.

Thanks and look forward to hearing from you soon.

Larry Perfect HVAC Estimate6.zip

[sktneer]

See if this is ok now.

[larrysdime]

Good Morning,

It was absolutely fantastic until I made some changes in the order of Items on the Duculator tab. Basically I changed it so that the more often chosen Items were at the beginning rows starting on Row 7 vs. them being at the bottom.

It was working great until I inserted some rows and moved the data up. Now I am getting a run time error and border issues again. I think I have the Duculator Tab pretty well formatted and should not be in need of further adjustment. Is there anyway to fix this error?

What I am concerned about is will inserting rows and adding equipment going to impact the code for the upper half? Manufacturers are always changing or adding equipment or we are always adding brands to the list on the equipment tab. I need to make sure that inserting rows and adding equipment to the equipment tab will not impact the macro performance. I have attached version 8 which has all the updates to the Duculator Tab in the proper Row (selection) Sequence. Once you run the Update Duct you will see the Run Time Error.

I know I have asked a lot of you and wanted to let you know what you have done is greatly appreciated.

Thanks,
Larry
Perfect HVAC Estimate V8.zip

[larrysdime]

Good Morning Sktneer,

I was wondering if you had a chance to look at mu last thread and version 8. I was holding off on finishing the project until you were complete with your code.

I have been studying your code trying to make sense of it, but I must say that code is above my level. Do you recommend a good introductory book to help me learn how to write code? I would like to advance my skills to another level and learn this aspect of working with excel.

Thanks again for all your work and I have reattached version 8 in case it is no longer available to you.

Larry

Perfect HVAC Estimate V8.zip

[sktneer]

Hi Larry,

Sorry to say but your approach is wrong here. I mean you asked for the help providing a sample workbook and describing your requirement. Then I wrote a code and provided you and later made some changes to take care some formatting issues as per your requirement. Then again you changed the sheet layout by adding more sections, I provided code for that even. Then again you inserted one column in one of the source sheet. And you know I am still not sure that you are not going to change something again.
Please don't take it otherwise but you need to complete the sheet layout first before going for a code to fulfill your requirements.
You might be aware that writing code takes some time and specially when it is not your own project i.e. it's difficult for someone to remember the sheets layout and the nature of the data present on them. So in order to amend the code one has to revise the already present code, sheets layout, data and requirements and that is really time consuming. I hope you understand.

I am still confused with the way you want data on Estimate sheet since you have already divided the estimate sheet into multiple sections and you can only do that if you know in advance that how many rows of data from the source sheets will be extracted on the Estimate sheet. Like for Update Duct section you have only 5 rows available. Does that mean only 5 rows max would be extracted from the Duculator Sheet?
Also you have 40 rows available for Update Equipment section. Does that mean you can have max 40 rows of data on Estimate sheet from the Equipment Sheet? Why this is confusing because both of your source sheets have plenty of data in them.

I would definitely try to provide you the workable solution provided you answer all my queries.
So please revise your sheet layout, run the already present codes keeping all the possible scenarios in mind and then examine the output on the Estimate sheet and think twice to see if anything is still left to cover. Once you are sure of everything, please let me know and I will continue my work to get the proper code.

[larrysdime]

Hi Sktneer,

I apologize for all that. My understanding of how code works is limited. I thought it would always bring the specific cells from what ever rows had values greater than zero without impact to text and line formats etc. on the "Estimate" Sheet. I also didn't realize that the Code would be impacted by rearranging data on the source sheet as I did with the "Duculator" sheet.

That would be my biggest concern. I don't see the Duculator Sheet changing much with the edits I have made other than values of hours and dollars which are not part of the data pulling over. However, the equipment sheet may be subject to rows being inserted as new products are added to the Vendor File. We may even introduce other Brands of equipment to the entire data list on the "Equipment" Sheet. As manufacturers are constantly changing their offerings, model numbers, prices etc. The Macro would have to be flexible enough to handle row deletion or row insertion on both the Duculator Tab and even more so on the Equipment Tab. So, I'm not sure if what I am asking for is doable or not. New rows of data may be inserted or deleted, but the Columns will always be as they are.

To answer some of your questions, There are 40 rows for Equipment, which in most cases the average would be about 8 to 10 line items selected for a quote. There have been a Few Instances on very large projects where I have seen an equipment selection go beyond 40 maybe once or twice. On the Duct selection Tab 5 rows should be adequate for the max # of Duct options selected, but maybe it could be expanded to six just in case. Or would it be better if the code would just force or insert more Rows into the Estimate sheet if they exceeded the 40 Lines allotted for equipment and let's say 6 lines allotted for Ductwork? This insertion of rows would just force all the other information down on the Estimate Sheet. It would be rare instances when this would happen, but I thought I would propose the question. Yes, the Data sheets have lots of Data, but on a given estimate Maybe 10 to 15 Equipment pieces are selected and no more than 6 Duct options.

I apologize for the numerous request and additional work it caused you. I guess the questions I have for you are:

1. Can the Code work if Rows of data are inserted or deleted?
2. If the Answer to 1 is yes, Than can the code be written to extend the rows to the required rows on the estimate sheet if Equipment exceeds more than 40 Rows and Duct exceed more than 6 rows?
3. Is this something that could be written in advance of me finishing the rest of the work book as Long as all the edits I am making are below the Ductwork section where the current macro exist? That of course would be based again on a positive answer on question 1.

If the Code will be affected by the insertion of rows on either the Duculator or Equipment Tab I'm not sure if we are going in the right direction since the source data will need to be modified on a regular basis. I can't see having to rewrite new code every time a Row is added or modified or deleted.

I can finish the design of the Estimate sheet if this helps. I also know that I will be modifying the data sheets in the future. However, the sort criteria of values greater than 0 and the corresponding columns we are using now will not change. The rows allotted and their formatting will not change either as they exist on the Estimate Sheet. This of course would be true unless you tell me there is a way to force more rows when equipment exceeds 40 lines and Duct exceeds more than 6.

Again, I apologize for all the re-work this has caused. I didn't realize that adding rows to the data source would create a problem for the code as long as the columns remained as they are. I also didn't realize that copying the data to the Estimate Sheet would impact existing Fonts, Font Sizes, borders etc. I thought it would copy and past only the values so to speak with no other Impacts to the estimate Sheet.

Please let me know what your answers are to my questions and I will proceed as you instruct. I just didn't want to waste our time if inserting rows of data to the source column would have an impact on the code down the road.

Thanks for your patience in working on this with me.

Larry,

Again, Here is version 8 if you need it.
Perfect HVAC Estimate V8.zip

[sktneer]

I just didn't want to waste our time if inserting rows of data to the source column would have an impact on the code down the road.

Larry

That should be the purpose of a good code and the code is designed dynamic enough to take care of newly inserted rows or if you delete rows from the source sheet. The code will cover all the data on the source sheet.

I will try to make the adjustments in the code to take care of your point 2 which I think is the only missing concept in the code.

[larrysdime]

Hi Sktneer,

Good to hear from you. I have been working on other aspects of the business to give this a break until I got good advise from you. I have reattached version 8 in case you don't have it available to you.

I did have one other question. Will the code search the entire source sheet for data or is it limited to the number of rows you tell it to search. I ask this because I may be adding quite a few more rows to the equipment sheet and I am hoping it will not matter and that the code will search all rows of the entire source sheet?

Thanks for picking this up again and I do sincerely apologize about my lack of code experience and how it impacted your design work.

Have a great day!!
Larry

Perfect HVAC Estimate V8.zip

[larrysdime]

Hi Sktneer,

I apologize for being such a pain, but I just remembered that per your recommendation I starting working on getting the file totally
done before you did anymore. That said, I changed one of the source sheets,the Duculator Tab to get it closer to its final design. I didn't want you to do work and then I delete that sheet and reinsert a newer duculator sheet later and not have it work.

So, with all this in mind I have attached version 9 which has the duculator source sheet near its final state. I may insert some rows but the columns will remain as they are from here on out.

Here is version 9 and thanks so much.

Larry

Perfect HVAC Estimate V9.zip

[sktneer]

Hi Larry,

I was working on your version 8 and I almost finished the modification in the code for the Update Duct and suddenly you attached the version 9. Which is completely different from V8.
Why different because you have merged the cells unnecessarily. Merged cells are never good for formulas and also for codes. So design your source sheets by removing unnecessary merged cells and reattach the modified version. Don't use merged cells unless they are requirement of your project. Have a look at your Duct, Equipment and Estimate sheets (all the three sheets) and remove the merged cells. Moreover why do you need cells to be merged, you can also increase the column width so avoid them specially where the actual data is present. It's ok if the header cells are merged but not the cells which have data in them.
I have stopped working now on your project and will only continue when I am satisfied that no unnecessary formatting and merged cells are involved in the source sheets.

[larrysdime]

Good Morning Sktneer,

Wow, I was working on the redesign to make sure I had it was amended to its final state (with the exception of inserting deleting row data down the road)and didn't realize you had already started on your end. I thought it best to send before you went any further. I did want to answer some of you questions.

I learned a long time ago that it is so much better to start an information document at a column width of 3. There are so many different types of information with varying column widths. If I expand a column width way out for 5 or 6 rows that require it then the entire sheet is then stuck with that column width. Else where on the page I may have a lot of small fields of data (Which I do) and the extra wide column impacts the efficient us of the form space in other areas of the page.

Then when I designed this way later when I tried copying data from one sheet to the other it would not paste due to "The Merged cells were not identical" or "it could not changed a merged cell" So I then decided to make the source data have the exact same column size with the exact same number of merged cells so that eliminated the paste errors.

So in short, greater Form flexibility = column width 3 and copy & paste problems = have merged cells identical on source sheet and Estimate sheet. Hope that all makes sense. If you are able to work around the paste issue by having the column widths wide on the source sheet and column widths small on the Equipment sheet that would be great.

I did at your request remove the merged fields from both of the Source sheets that we are working with. However, I Did Not un merge any cells on the "Estimate sheet" where the equipment is going to be copied to. I wasn't sure if I should so I left the fields merged on the estimate sheet in the area we are copying the data to. If you feel it would be better to unmerge those fields on the estimate sheet please feel free to do so.

Attached is Version 10 where I unmerged the data on both the equipment and duculator sheets. I sincerely hope this helps and is what you would like for me to do.

Thanks again,

Larry

Perfect HVAC Estimate V10.zip

[sktneer]

Hi Larry,

Please find the attached workbook (version 10) with the modified code.
You are not supposed to insert or delete any row below row 24. Of course you can adjust the column width without merging cells to fit within the print area if you are required to take out the printouts.
So now all the possible rows which meet the quantity criteria from the Equipment Sheets will be extracted to Estimate Sheet and same is the case with Duct Sheet.
Now after running the code once if you run the code again, the code will update the rows on the Estimate sheet based on the source sheets. i.e. if you add some more rows or delete some rows on the source sheets, that change will reflect on the Estimate sheet.

I assume this will be your final code.