Excel Macro for conditionally copying cells

[Brett555]

For my problem, I've found similar questions and answers with macro code online, but with my limited macro-writing experience, each time I try to implement another person's code into my file, it does not work. Here is my problem:

My 'Data' worksheet has data in cells B8:D640. Column D is an indicator of hours, which can range from 0 to 999,
For each row of data, if hours is greater than 0, I want the row to be copied into the 'Summary' worksheet into cells B8:D640.

But in the Summary sheet, I want the blank rows deleted. For example, if only 2 rows from the 'Data' worksheet have hours > 0, I want those two records to appear in rows 8 and 9 of the 'Summary' worksheet. So the 'Summary' worksheet ends up being just a copy of the 'Data' worksheet, except with the rows deleted in which hours = 0 or null.

I am using Excel 2010. I've copied a file with the 'Data' sheet and the result I would like to obtain (after running a macro) in the 'Summary' sheet.

Thank you!
Brett

[Ace_XL]

Good day,

Instead of copying the whole set of data and deleting rows with hours =0, why not copy data that has hours >0 in the first place by use of an autofilter? Try the below code (its relatively easy to understand)

Please Login or Register  to view this content.

[HSV]

Please Login or Register  to view this content.

[Brett555]

Thanks for both of your responses. I used Ace_XL's solution before I saw HSV's solution, and it worked perfectly. A recent update to my task calls for a little more complexity that I do not know how to program. Instead of pasting the filtered data into the 'Summary' sheet, I want to have the macro filter the data into the sheet that is was currently active when I press my button that runs the macro. I will also have the name of the active sheet entered into one of the active sheet's cells, so it may be easier to reference that cell rather than the active sheet so that the Excel knows where to paste the data. Thank you again for your help.

Brett

[HSV]

You have more sheets to be filtered ?, and you want the filtered range to be paste in the activesheet?
Maybe you can upload a new example.

[Brett555]

HSV - Thanks for your reply. I've uploaded a file to more clearly show what I mean. In the "Summary" and "907" worksheets, I have a button which controls the Update macro. But the macro always pastes the values into the "Summary" worksheet. If a user presses the update button in the "907" worksheet, I want the values to be pasted into the "907" worksheet instead. Ideally, I would like the values to be pasted into the worksheet that has the same name as the value that is entered into cell D3 (job number). This is so that the same macro will work correctly in many different worksheets. Thank you for taking a look.

(by the way, I found Ace_XL's solution worked a little better because HSV's continued to add data rows and did not clear out the old data first - so that is the macro code that is in the uploaded file)

[HSV]

For both buttons.

Please Login or Register  to view this content.