Modifying Copy - Destination Range throws App Error '1004'

Greetings,
I created a working mock up of code I found on the web which consolidates blind survey results for any user ID.
It worked fine as it stood, but I wanted to modify the code from copying an entire row - to copying just the results across the number of columns that had data in it.

Please Login or Register  to view this content.

I have tried lots of permutations of Range properties and Copy/Paste qualifications - but I'm missing something very obvious with the Object variable and I can't figure it out.
Any help is appreciated. Workbook is attached.

Mark

I have adjusted the code
You have number 6 twice on sheet Survey-8-01-10.

Thanks MarvinP -
I totally missed that.
My goal was not to copy everything that might be in that row from A-XFD,
but to make the Columns range more dynamic by looking “xlToRight”, to find the last data column in that particular “found cell” row.


Thanks AB33 -
In this format, “Resize” works only as a static range. If this could be made dynamic, then my problem is solved.
I’ve had success using this code as a stand alone test:

Please Login or Register  to view this content.

Unfortunately, I was unable to incorporate it into the ” Do Loop”.
Here are four permutations that I attempted and the resulting errors thrown:

Please Login or Register  to view this content.

At issue, I believe, it the Object Variable “rFoundCell” and possibly how “ActiveCell” is used in this case. Whether that be its assigned value, returned value or whatever. I don’t have that deep of an understanding of VBA intricacies to debug this problem.

What mm I missing here?
Any ideas?

Thanks--
Mark

Thanks MarvinP -
I totally missed that.
My goal was not to copy everything that might be in that row from A-XFD,
but to make the Columns range more dynamic by looking “xlToRight”, to find the last data column in that particular “found cell” row.


Thanks AB33 -
In this format, “Resize” works only as a static range. If this could be made dynamic, then my problem is solved.
I’ve had success using this code as a stand alone test:

Please Login or Register  to view this content.

Unfortunately, I was unable to incorporate it into the ” Do Loop”.
Here are four permutations that I attempted and the resulting errors thrown:

Please Login or Register  to view this content.

At issue, I believe, it the Object Variable “rFoundCell” and possibly how “ActiveCell” is used in this case. Whether that be its assigned value, returned value or whatever. I don’t have that deep of an understanding of VBA intricacies to debug this problem.

What am I missing here?
Any ideas?

Thanks--
Mark

There are a couple of ways to make the range dynamic. If you have a header in each sheet, this option is the easiest one. Try it

Maybe:

Please Login or Register  to view this content.

Thanks AB33!
I had thought of trying something to that nature but just couldn't figure out where to begin.
YourCode:

Please Login or Register  to view this content.

Question: Why would the ActiveCell code work as a stand alone test, but not with an object variable?
Any ideas?


Hi John-
I tried implementing you idea w/o success.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is an interesting puzzle that requires more experimentation.

I appreciate your quick responses and helpful ideas.
Any further input is most welcomed.

Mark

Mark,
Question: Why would the ActiveCell code work as a stand alone test, but not with an object variable?
Any ideas?
I have not used any word "Active". Your code was good, except I have added a single line.
It is terrible idea to use ActiveCell. ActiveCell could be anywhere the cursor is. What happens if I click any in cell while the code is running, it becomes an active cell, so avoid using it.
Your code was very good, just remove active