Move rows without CF using PasteSpecial

[[Jimmy]]

Hi guys,

First post but still a frequent board user...

Here's my issue!

I have a workbook with 2 spreadsheet, let's say A & B.
I now use a VBA-script to go through all rows in that spreadsheet A1 and if column e of that row contains the value "0"
I then move it to spreadsheet B. All other values remain in its origin place in sheet A.
I've also added 4 CF-rules to the active area (let's say $A$1:$E$20), which means that depending of the value in a certain columns
it shifts the background color between 4 different colors.

It all works pretty fine, but VERY slow since I use a loop function i found online, and as I'm pretty new to VBA I don't have the skills
to build the same thing from scratch. So

My issues are:
1) Is there a better (faster) way to get the same result? Clean the code or rewrite it, if so -how?
2) When a row is copied from sheet A to sheet B, it pastes everything including the CF-rules, I just want to paste
the formulas (and thereby the textvalues and the formulas my cell contains) from sheet A to Sheet B (expect the CF).
The reason to this is that I want to use the CF-rule that are applied in Sheet B.

What happens now is, that it ADDS the four rules rules mentioned above, from sheet A to Sheet B, where sheet B already contains the same rules) but in an expanded area ($A$1:$E$200). Where does it do wrong?

Heres my code:

Please Login or Register  to view this content.

I've steped into it 100 times, and it seems that it's on the line...

"ActiveSheet.Paste"

...where it pastes everything (incl. the CF) that I DON'T WANT, and then 2 code rows later, it pastes everything aging,
but this time it pastes it what I'm asking for.

I'm not sure if the code is optimized for this purpose, so please review it and get back to me with any changes that might help.

Thank you in advance!

PS. I'm using Excel 2007 if that helps

[RaulRodriguez]

Try this code:

Please Login or Register  to view this content.

Don't forget to click on the Star below the posting to show your appreciation for the help received and mark the posting as solved.

[[Jimmy]]

Perfect!

It works just great! Probably 5 times faster then my original code! =)
The only thing that didn't work as I wanted is that it copied all info from sheet prospect to sheet databas and put the info in cell a2,
and all existing information in that sheet was deleted.

But it was easy to change it to what I wanted, (which was to put all the new information from sheet prospect and place it one row below the last information row in sheet databas)... I just used my old cellname (sistacell) which handles that thing...

The final code now looks like this!
Thanks for the help Raul!!

Please Login or Register  to view this content.