Hello all, I have a sheet called "fixtures" in my workbook.
I column P i have a cell value of either 0 or 1.
What i would like to happen is every time i exit the fixtures tab - for all rows with a value of 1 in column P the range B to O to be selected, and copied into a sheet called Data.
So to summarize, assume cell P3 and P6 have a value of 1. When i exit the fixture tab cells B3:O3 and B6:O6 would be selected/ copied and pasted into sheet Data.
What about when you exit the next time? Wouldn't the same rows get copied over to that sheet again?
Each time you exit, is the data being copied to the bottom of the "data" or would the "data" sheet be cleared each time and all the data being copied to "data" would be creating a clean new listing from "fixtures" each time.
Think about it. Let us know what the result should be time after time.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
This will copy columns B through O when P contains a value of 1 when the "fixture" tab is deactivated (when you navigate to another tab).
Please Login or Register to view this content.
Why the Loop? When evaluating for the same value in all rows in a single column, the AutoFilter can be used to grab all the rows in a single copy/paste command.
I guess I just like loops
Can you demonstrate the single line method? I know how to do a two line method, but could not figure out the single line.
Please Login or Register to view this content.
Thanks all for your input, Jbeaucaire you make a good point about the repeated copying.
I already have a bodged code which sets the auto filter and then runs through some macro's and finally delete's the copied text. I just wondered if there was a quicker way to do this.
I will have a look at the above and see if i can maniplulate my coding.
Try this:
Please Login or Register to view this content.
MDBCT, You misunderstood my comment, I said a single copy/paste command, not a single-line macro. What you constructed is exactly what I had in mind.
You'll want to work something in there to insure you get no errors or incorrect behavior if the autofilter leaves no rows visible...meaning there are no matches.
Yes, I did misunderstand...
I agree about the no visible rows - got distracted by work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks