Conditional Copy Paste function

I am completely illiterate in visual basic, and my boss wants me to help him with some excel macros.. I have some history in programming Java and C, but I still need someone to help me with this. Please break this down for me, as if I was a 2 year old.
I need a conditional copy and paste macro (I'm not sure it even needs to be a macro, we just need to add functionality into an excel sheet). What I need to happen is that when you hit this button on a sheet, it checks a certain cell on the sheet. If this cell has a value greater than "0", copy and paste all the cells in range from "aa" to "bb", and paste them into a second sheet. Then move onto a different cell from the first sheet, and if that cell has a value greater than "0", copy and past all cells from range "yy" to "zz", and paste them on the second sheet, below the earlier paste. This needs to be repeated to check a certain number of cells.
Any help is GREATLY appreciated.
~Adam D.

Try this. If there is some logical connection between the cells to be checked, the cells to be copied, and/or the location they should be copied to, this could be simplified. But, this should work for the most general case.

Please Login or Register  to view this content.

To tie this to a button, you have 3 options:
1. use the drawing toolbar, draw a rectangle and make it look like a button, right-click, assign macro, select this macro
2. use the Forms toolbar, draw a button, it will automatically ask you to assign a macr, select this one
3. use the Control toolbox toolbar, put the workbook in design mode (first button on the control toolbox toolbar), draw a button, right click, view code, this will create an Event handling macro for the button, put this code inside, delete the "Sub Test" and the duplicate "End Sub"

Thank you very much, MSP77079! With the comments and all I was easily able to figure everything out, and it works like a charm.
I'd like to add something to this, though. There are something like 200 potential rows of pasted material going into the second sheet, so that makes for a lot of wasted space if only certain cells are going to be copied over.
There are 2 ways that I can think of to fix this.
What seems to me would be the easier logic is to check for empty cells/rows within a certain range on the second sheet and collapse/delete them, so that everything is pushed together.
The second way would be to not set definitive ranges for the copied material to be pasted into, but to have them somehow copied into the first available empty area on the second sheet.
Could anyone please show me how to do this? Once again, I would be greatly appreciative.
~Adam D.

Are the values going to be near each other or dispersed? If the values make a table (even if there are some blank cells), you can use the "CurrentRegion".

To see what this looks like, select any cell, then
+ use Edit >> GoTo (or F5) to bring up the GoTo dialog
+ select "Special" (at bottom left of the dialog)
+ click "on" Current Region (third from bottom left)
+ click "OK"

If this seems to be what you want, then turn on the Macro recorder (Tools >> Macro >> Record New Macro) and repeat the steps above. This will give you the VB code for referencing the Current Region of a cell.

Evidently the values are dispersed. When I use goto to select a region, it only selects a group of cells if they were copied and pasted over together.. if current region is selected in one of the empty cells, it does not select anything.. I'm not sure how this would help me at the moment, although I'm sure this tool will be helpful to know of in the future.

What I need is something that deletes an entire row only if the entire row is empty. When I select all blank cells and delete them it scrunches everything up into an unreadable mess. A Visual Basic script would work just as well as instructions on how to make a recroded macro.

I tried copying and pasting the macro in the tips section for deleting empty rows, but when I tried to run it it would not show up.
Once again, thank you very much for all of your amazing help on this.
~Adam D.

Something else..
I forgot to mention this, but is there a way to change the traditional paste in your script to a "paste special" for values? There are some cells that only show up correctly if you do it that way.
~Adam D.

I will answer the last question first, since I know the answer to it ...

Substitute the code below for the copy/paste portion.

Please Login or Register  to view this content.

I need to soak on the other question a few minutes.

Adam, this might be a lot more complex than it needs to be. But (as in the first example), it should work for the most general case.

Please Login or Register  to view this content.

Astounding!
It all works! Thank you very much!
But there is still one problem.. When the paste special goes over, it removes all the formatting from the cells.. so the values come over, but the colors on certain cells and the font changes don't copy over.
Is there a way to get both the values from the cells that have formulas and the formatting of the cells to both paste over with one function?
Most Appreciative,
~Adam D.

Hi Adam,

The reason the last version brings only the value (not formatting) is because of this line of code:

Please Login or Register  to view this content.

This only takes the value from one cell and puts it in the other cell. It ignores formatting entirely.

Here is the easiest way to get both value and all formating. Just replace this one section of code:

Please Login or Register  to view this content.

The line of code with ".Copy" will copy the everything; but, that leaves formulas that you do not want. The next line brings the value only.

Alright! You've worked wonders for me.
So this worksheet, it's comprised of 26 groups of cells, with each group containing a different number of rows. In the future, we might need to add additional rows to some of the groups. Right now, if someone were to add in some rows, I would have to go back and manually change the script to check for different cells, and to copy different ranges of cells.
What we want is for users to be able to add these rows with the click of a button, and for the copy and paste function to still work without me having to manually change the script. Is there a way to do this?
Thanks,
~Adam D.

The easiest way I can think of is to Name the ranges (you can use Insert >> Name >> Define, or use the Name box to the left of the Excel Formula bar).

Once you have named the ranges, a person can insert rows (or columns) and the Name will shift to the new position (just like the data will).

I am pretty sure that (will test and report back) that you can simply replace the lines

Please Login or Register  to view this content.

with something like:

Please Login or Register  to view this content.

And everything will work just as well as if you had used cell addresses instead of names. Infact, since Names (normally) have Workbook level scope, you would no longer need to reference ws1 vs. ws2. Excel will already know where the range is just from the Name.

Is this the sort of answer you were looking for, or something different?

I took the last version of code and changed the following 2 lines to:

Please Login or Register  to view this content.

Created the 6 Named Ranges. It worked just like before. Don't know if this is what you had in mind or not.

That is EXACTLY not what I was thinking about.. it's so much more simple and powerful that it blows my mind. Thank you VERY much for all your help! Excel is such a wonderful tool!
Here's yet another excel problem of mine that you might be able to answer.. When I protect my worksheet I can no longer hide or unhide my groups. Someone else showed me this bit of code :

Private Sub Workbook_Open()
With Sheet1
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

but didn't really explain how to use it.. Am I supposed to somehow modify it to fit my worksheet? Would that even work for Excel 2002? Would this script automatically run when I open the workbook, or do I need to somehow call it?
Or is there a better (different?) way to get around this problem?
Thanks once again,
~Adam D.