Copy and paste except null value formula results

**Xrull** · 02-19-2009, 07:17 AM

From time to time the data on worksheet x changes and it is picked up by worksheet y and I need to copy and paste the new information to sheet z. I've been fiddling with the code to get it to paste the values and pick up the new values, but it won't.
I need to adjust the code to copy to the last column, and the last row, and copy just the values. I’m unable to do t his.
I've attached the sheet so you'll see that the data will only copy once.
Go to worksheet z and press the command button.

I got the code from this site:

HTML Code:

Please Login or Register  to view this content.

If it is not too much explain to me what I'm doing wrong, and how your code works.
Thanks for your help,
Xrull

**DonkeyOte** · 02-19-2009, 07:24 AM

If you're just copying values why not simplify:

Please Login or Register  to view this content.

Change ranges to suit.

**Xrull** · 02-19-2009, 07:51 AM

Donkey Ote,
I need to accumulate the data. The range on Sheet x varies , and I want to copy the values if it goes to row 10000 (more columns to the right, and more rows down, or inwards columns, or upwards rows).
If there is a way to adjust your formula to paste to the last line on worksheet z would be great.
Thanks,
Xrull

**DonkeyOte** · 02-19-2009, 08:02 AM

So are you saying Z is to act as a database of all the data that has ever been in X - ie you run and Z becomes Z + whatever is presently in X ?

**Xrull** · 02-19-2009, 08:16 AM

Donkey Ote,
You are correct. New data is dumped to x every month, I use y to pick up the data, and z accumulates the data serves as a database for the year.
Thanks,
Xrull

**DonkeyOte** · 02-19-2009, 08:26 AM

I would still adopt a similar approach... ie set your copy range to be as big as you want and simply offset from the last entry and resize according to the copy range... blanks will simply be blanks.

Please Login or Register  to view this content.

Does that make sense... so you select the first blank row on Sheet Z and resize according to the dimensions of your copy range which are predetermined to encapsulate all possible entries on X and you insert the values accordingly... ie blanks on X are simply blanks on Z
(obviously don't use the entire sheet as you can't copy that on top of existing data)