Noobie~ If in column, copy row

I am a noob!

When I press the call to action button On sheet9 If column E = 1 I need to copy the row to sheet10. There will be 4 or 5 rows that will get moved over. I will have a individual button for E=1 through E= aprrox 100.

Yes 100 buttons unless you have a better Idea.

If someone could please help or at least point me in the right direction.

Hi Dohara,

I am struggling to visualize the description of your spreadsheet?
Can you try again to describe in more detail what you need to do?

Also, you say If Column E = 1, is that cell E1... please be more descriptive.

TX

Thanks for responding. by Column E, I mean cells E2 through E?. I need it to go all the way down E and If it =1 than to copy that row (ex. A6 trough E6) to Sheet 10 starting at row 2.

Here is an Example of sheet 9. Sheet 10 will be the exact same but it will only have the group for the button I pushed. (In this case, Group 1)
Name Weight Age Team Group
Random 1 Random 1 Random 1 Random 1 1
Random 2 Random 2 Random 2 Random 2 2
Random 3 Random 3 Random 3 Random 3 1
Random 4 Random 4 Random 4 Random 4 3
Random 5 Random 5 Random 5 Random 5 2


To go a little more in depth. I have approx. 10 sheets. I have worked backwards on the project. This is the very beginning. I will split up about 300 names into groups of 4. From there I will press the "Button". I will start with the first button (group 1). Group one will be fed to Sheet10. Some info on Sheet 1&2 will = certain information on Sheet 10. Certain info on 3&4 will equal certain info on 1&2. Certain info on 5&6 will equal certain info on 3&4. On Sheet 1&2 there is a print button that will print sheet 3&5 or 4&6 depending on certain criteria. All this is done and set up. I just need the final marco button finished.To sum it up. I will be printing sheets 3&5 or 4&6 for each group. After each group is printed I will hit a clear button (already done) and then press group 2 button. After 2 is printed I will press clear and than the button for three. So on and so on.

I hope this help but I have a feeling I may have complicated it more for you.

I will build a small model based on your sample data.
I could suggest already to have just one button on sheet 9 and have it read from a cell with the group number in it. That will avoid you having to hook up the same macro to 100 different buttons (and have to maintain those buttons) "Argh"

I'll send the sample workbook with a macro to demo what I mean shortly...

Thank you.

Is there a way, and I am not sure if it is possible, But when I hit the button a message box come up with the ability to enter what group I want to push to sheet10?

Have a look at the attached Sample.
I have updated it to prompt for the group value as requested.

Sample.xlsm

Perfect!!!!!!!!! You Rock!!!!!!!!!!! How do I mark it solved lol

Originally Posted by dohara

How do I mark it solved lol

I have no idea? I'm a noobie here too... LOL
Glad its working.

I have a ? What string would I run to have sheet10 pop up after it runs?

Here is an updated Sample based on your request...

Sample.xlsm

Perfect.

One more question lol. Just basic excel. I have a cell (Average weight) that =(Sheet10!B2+Sheet10!B3+Sheet10!B4+Sheet10!B5+Sheet10!B6+Sheet10!B7)/4

Sometimes I need it to be divided by 3,5 or 6. Depending on how many entries there are that are not 0. Is it possible to do automatically without doing it manual or using a marco?

Hi Dohara,

No worries with the questions...That's why this forum is here

You can use an AverageIf function to do that quite easily. So that you have a range of values in A1:A6 and two of those values are 0's. Yse either one of the folowing formulas in A7 (or somewhere else):

=AVERAGEIF(A1:A6,">0",A1:A6)

OR

=SUM(A1:A6)/COUNTIF(A1:A6,">0")

Perfect.

I appreciate all your help.

Do you know if there is a way to have a floating button. Basically as I scroll down the page I would like to have the button scroll as well.

If so what is the string?

Another question. In the process button, If I press cancel It assumes zero, and transfers everything without a grouping number. I looked in the code, but didn't see a way to change that. Is there a way to change cancel to assume something crazy like "7th9u", so cancel will literally mean cancel and not move anything?

A button can move and size with cells, but not float.

A workaround is to freeze the panes:
You can simply set a freeze panes on the rows.
Select cell A3 and from the View Tab click on the Freeze Panes button and choose Freeze Panes.

Added bonus: You can select cell A5 and freeze the heading so they too stay on the page.

It wasn't exactly what I was looking for but I made it work. Once again ty.

Glad you came right
It was a pleasure to help.

Just an afterthought: You can trigger the macro with a shortcut key, or place it as a button in the Quick Access Toolbar of Excel. That way the hotkey or the button is always available to trigger the macro.

That's not a bad suggestion.

Another question. In the process button, If I press cancel It assumes zero or nothing, and transfers everything without a grouping number. I looked in the code, but didn't see a way to change that. Is there a way to change cancel to assume something crazy like "7th9u", so cancel will literally mean cancel and not move anything?

Ooops... LOL...
That is something I forgot to add. I'll put that code in and send a new sample file.

Np,Ty. I will have to show you a sample of my finished work when all is said and done.

I'll just paste the updated code instead of the whole workbook.

Replace your current code with this code:

Please Login or Register  to view this content.

Originally Posted by dohara

Np,Ty. I will have to show you a sample of my finished work when all is said and done.

LOL... It does become an artwork of sorts...

Yes it does. Such a simple program, yet I am very proud of it.

Yet another question,lol. hopefully last one. Right now the box automatically has one. I see that in the code. How would I make it where it automatically +1 to the last selection. Ie I choose one, the next time it opens to, and so on. I assume just adding a + will do me no good.

Yes..you have seen in the code that a default number is set (which is 1).
To do what you ask would require a Static Variable set in the code that remembers the last number and adds 1 to it each time the code runs again. This will continue to increment each time. IOW, if you have run it 1000 times, the value will be 1000. Is that very practical for the purpose of this macro?

Sort of. Mainly because once I do a group, I will move to the next, and rarely repeat a group. It will take away a step (entering a number) when I am doing everything, but at the same time gives me the option to redo a group if need be. I assume adding a static variable will completely change the code?

How do I attach the spreadsheet so you can see it?

Oooo...do I get to see your Masterpiece
In the reply window, click on the button called: Go Advanced
Then in the Advanced window, with more toolbar buttons, click the attach button (small paperclip)
Browse for your file, upload and then insert the uploaded file into your new post.

I played with the static variable a little bit, couldn't get anything to work with what I was finding on google.
I tried to attach the "Master Piece", but it said the file is to large. 5MB. If you want to pm me your email ill be happy to send it.
A lot of little nickpick stuff left, but for the most it is done.

If its that large, don't worry too much. I'm honoured anyhow that you wanted to share it
I'd probably not understand the context of the info either.

I'm glad its working and improving for you. That is the value of Excel...it's flexibility and tools to allow more productivity and smarter working...
I'm happy I could help along the way.