Macro to transfer data as values to another sheet based on the result from a formula

[bpiroma]

Hi,

I found a macro (created by ky095n) in one forum that I tweaked so I can use it to transfer data from one sheet to another sheet (named: Closed) then delete that data in main sheet.
My problem is that I want the transferred data as values (no formulas).
Also, right now the code works fine if I type the text "Closed" but if the text is a result of a formula, the code doesn't work.
I am new in excel.
Hope I was able to explain my problem.
Thanks in advance.

Please Login or Register  to view this content.

[Chippy]

For copying values, not formulas, I did a copy-paste special with the macro recorder and then edited the code generated to remove the Select and Selection parts. The macro recorder is the best way to generate code if you are not sure of the syntax or Excel object names.

In which column is the text "Closed" the result of a formula? In the following code I've assumed it is column A.

Please Login or Register  to view this content.

If it isn't column A, change the "A" in the line | If .Parent.Cells(.Row, "A").Text = "Closed" Then | to the appropriate column letter.

[bpiroma]

Hi Chippy,

Thanks for the reply.
It is now working but how come when it paste starting on row10 then when I deleted the sample data and tried it again, it started pasting in row11?
Is there something that I am doing wrong?
Thanks.

[Chippy]

It works for me. It always pastes to the first empty cell in column A on the Closed sheet (determined by looking in column A from A20 upwards). You can see which cell it will paste to by adding the following line before the PasteSpecial:

Please Login or Register  to view this content.

[bpiroma]

Thanks Chippy..
It working great now..