PDA

View Full Version : Solved: copy a cell value not the formula??



PaSha
11-21-2007, 01:24 PM
:think::think: hey guys... me again ...

hope i'm not anoying : pray2:...

i wonder if someone could help me... ahmm actually i know there is someone,hehe..

the problem is this ... i have some tables in excel... which during the month is generated whit some values,every day... at when the month is ower i have to take this table and copy the values, becouse next month the same table is beeing used i can't just link the values...

so i trayed to copy some cells, for the beginig, but it aint wanna work like i want... the values are sometimes 0, or REF, or VALUE...
actuaally it copies the formulass also in this cell... but i don't need them...

how can i copy the values, but not the formulas???

my code looks for the beging like this:



If DAY(Range("j2")) = 31 Then

Sheets("Woche 1").Range("c20").Copy Sheets("Jahr").Range("c62")

End if



i just trayed this one cell, but it isn't functioning like i want... so hope you understand me and can help me also ...

thanks

RichardSchollar
11-21-2007, 01:58 PM
You could do that easily with:



If DAY(Range("j2")) = 31 Then

Sheets("Jahr").Range("c62").Value = Sheets("Woche 1").Range("c20").Value


End If

Richard

PaSha
11-21-2007, 02:15 PM
no that doesn't work, something is wrong, and i can't get the answer what...

lucas
11-21-2007, 02:25 PM
Maybe pastespecial after copy:

untested
If DAY(Range("j2")) = 31 Then

Sheets("Woche 1").Range("c20").Copy
Sheets("Jahr").Range("c62").PasteSpecial Paste:=xlValues

End If
I may have the copy and paste backwards too...not sure where you are copying from and pasting to.

Bob Phillips
11-21-2007, 02:30 PM
no that doesn't work, something is wrong, and i can't get the answer what...

That should work, so if not, what are you seeing, a run-time error, cell error, no value, a wrong value?

PaSha
11-21-2007, 03:46 PM
That should work, so if not, what are you seeing, a run-time error, cell error, no value, a wrong value?

also with that code i got erro's like befor ...

run-time error 13
type mismatch

Zack Barresse
11-21-2007, 04:17 PM
Type mismatch on which line though? What line was highlighted when the error broke?

PaSha
11-22-2007, 03:16 PM
ok sorry for my late replay... i just came home now... and saw you replays...i wanna first thank you all...

and now... i trayed to manage this simple code to work, but it doesn't work...

so first i used this code : Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Day(Range("f2")) = 30 Then

Sheets("Woche 1").Range("c20").Copy
Sheets("Jahr").Range("c62").PasteSpecial Paste:=xlValues

End If


End Sub


it gives me a mismatch and highlights this perticular line:

If Day(Range("f2")) = 30 Then

ok, so i thought maybe it is becouse of the range so i just used If Day(f2) = 30 Then and it gives no error, but it doesn't copy me the value...what it does is, that the cell were the value should be pasted, it deletes the zero (0) in it and the cell has then nothing.


so now i don't understand this...

lucas
11-22-2007, 03:33 PM
Sheets("Jahr").Range("c62").Value = Sheets("Woche 1").Range("c20").Value

will copy the value only from c20 of sheet "Woche 1" to c62 of sheet "Jahr".

is that what you are trying to do or are you trying to do the opposite. copy value only from c62 of sheet Jahr to c20 of Woche 1? If so then try this:
Sheets("Woche 1").Range("c20").Value = Sheets("Jahr").Range("c62").Value

PaSha
11-22-2007, 03:43 PM
hey lucas thanks for the replay...

i saw your code... but i already managed to see were the mistake was...
i have specified some stupid cell which to copy, not the right one...

about your code...that you wrote... there is no copy in it... is that really copying values??? or is it just linking the values... becouse, if after day 30 i make some changes in that cell that i copied into another... the cell where i pasted it is also gonna change or not??? i know that when u use copy it doesn't ... but if yours also doesn't than maybe it better to use???

ok i managed to solve this part so the code looks like this now... sorry for mystupid replays, maybe i was just to tired to see it ... in future i'm gonna first think and then paste :banghead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Day(Range("j2")) = 20 Then

Sheets("Woche 1").Range("G20:V20").COPY
Sheets("Jahr").Range("c62:R62").PasteSpecial Paste:=xlValues


End If


End Sub

lucas
11-22-2007, 03:52 PM
hey lucas thanks for the replay...

about your code...that you wrote... there is no copy in it... is that really copying values??? or is it just linking the values... becouse, if after day 30 i make some changes in that cell that i copied into another... the cell where i pasted it is also gonna change or not??? i know that when u use copy it doesn't ... but if yours also doesn't than maybe it better to use???


Hi PaSha,
It is the same bit of code that Richard offered you in post #2 of this thread. It does the same as copy.....there is no real need to copy in this case and it will not change unless you run the code again....same as the copy.

PaSha
11-22-2007, 04:10 PM
Hi PaSha,
It is the same bit of code that Richard offered you in post #2 of this thread. It does the same as copy.....there is no real need to copy in this case and it will not change unless you run the code again....same as the copy.

:thumboh yeaah... this is cool ... it works like you said... thanks richard and also thank you lucas...

this works even bether ... becouse the after the code with copy statement the range was highlightet until the date did'nt chnaged and i couldn't make any changes to that sheet, i don't know why ... but with this code it works great ... without any problems ... sp thank again ...

the code now:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Day(Range("j2")) = 22 Then

Sheets("Jahr").Range("c62:r62").Value = Sheets("Woche 1").Range("g20:v20").Value

End If

End Sub



now i only need to calculate the last day in a month ... when this code should run ...

but for tonight it's good... now i need a sleep :cloud9:

thanks people ... you're really the best ...

lucas
11-22-2007, 04:34 PM
Glad to help PaSha. Start a new thread when you get some rest...