Find, Copy and Paste Values

[speedomes]

posted here also. http://www.mrexcel.com/forum/excel-q...k-correct.html and http://www.ozgrid.com/forum/showthread.php?t=181168



Hello fellas.
Could anyone help me out with this little problemo.
I have a sheet which randomizes letters (A,B,C) in cells B1:Z1. This is copied down to 10,000 rows.
In cell A2:A10000 I have a SUMPRODUCT formula which shows different numbers everytime the sheet randomizes.
I then have this macro that stops the randomizing when a zero appears in A2:A10000

Please Login or Register  to view this content.

What I would like to happen is that when the above macro finds a zero it automatically copies and pastes ALL ROWS where a zero is (as there may be more than one) to Sheet2 in the same workbook. It has to paste values only though as the rows contain

Please Login or Register  to view this content.

and the rows would randomize again when pasted.
I really hope that is clear
Thank you in advance for your continued help.
Mike

[LaffyAffy13]

Well, if you only are looking for 0's in Column A, you could always do another loop that looks for 0's.

Please Login or Register  to view this content.

[speedomes]

many thanks for your rapid response LaffyAffy13, it's very nearly perfect!
However, it paste the rows with

Please Login or Register  to view this content.

so the information in B:Z changes

[LaffyAffy13]

So sorry, I forgot about that tidbit. Try replacing "ActiveSheet.Paste" with

Please Login or Register  to view this content.

[speedomes]

Hi, thank you. This is what I have now but returns error

Please Login or Register  to view this content.

[LaffyAffy13]

Which error are you getting? Try this

Please Login or Register  to view this content.

[speedomes]

Hi again, compile error, sub or function not defined and it highlights the PasteSpecial

[LaffyAffy13]

Oh man, I feel dumb haha. Try this

Please Login or Register  to view this content.

[speedomes]

lol, no probs. However, sorry to say this error is returned.

Run-time error '1004' PasteSpecial method of range class failed

[LaffyAffy13]

Yeah I can't run any of these macros because you haven't given me a sample workbook to test any of them. So idk. This is my last guess, if it doesn't work can you upload a file?

Please Login or Register  to view this content.

[speedomes]

Hi again. I have uploaded linked my workbook to maybe make things a bit clearer.

testsheet (2).xlsm

As you will see it is populated with CHAR(RANDBETWEEN(65,67))to produce rows of random A,B,C's
I have a SUMPRODUCT formula that gives me numbers in Column A.
What I would like to happen is that when a zero appears anywhere in Column A, excel will copy and paste into sheet2 all the rows that have a zero in Column A.
The biggest issue I have is that because it's CHAR RANDBETWEEN the rows data obviously changes when pasting into Sheet2.

Thanks to LaffyAffy13 this is the VB I have at the moment which does the job of finding all zeros and pasting the rows into Sheet2 brilliantly, but clearly the row data changes because of the volatile CHAR(RANDBETWEEN) code.

Please Login or Register  to view this content.

I would appreciate a solution if any of you gurus can come up with one.
Mike

[speedomes]

Thank you to cytop on another forum for providing solution below. Also thank you to LaffyAffy13 for providing the original code. Much appreciated

Please Login or Register  to view this content.

[6StringJazzer]

Speedomes, we require links [http://www.ozgrid.com/forum/showthre...1121&p=676763] if you post the same question to other forums:

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).