how do I copy the value of a calculation but not the fomula ?

  • Thread starter Thread starter pete mann
  • Start date Start date
P

pete mann

:confused:

Hi All,

I am in need to assistance once again, could some please help me with
the following

I am wanting to copy a value that is worked out of a calc into another
cell ? The problem is that when I copy the value, it copies the
calcution and not the value !!!!!!!

any suggestions will be more than appreciated
 
You didn't mention or show your method of copy or code.
in code
range("a1").value=range("b1")

manually copy/paste values
 
Hi Pete,

Copy the cell and then Edit>PasteSpecial>Values.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi

The method that I am using is ctrl C followed by ctrl V when I hav
selecred the cell I am wanting to paste into. The value/calc of th
code is =F5+1 and then this works out a value eg 101. After which
would like the value of 101 into a another field, but when I used th
copy function it copies the calc across and value, where i only wna
the value!!!!!!

The objective is that I want to use a counter and it is onl
incremenedt when saved. Do any of you have any thoughts/ideas on an
other methods to achieve this ?

I would like to simple create a counter incremented when saved and th
previuos value moved to sheet 2

any ideals ?




:confused
 
Pete,

You could have a workbook name that is incremented before saving the file.
This code in ThisWorkbook code module shows a way to achieve this


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim myId As Long

myId = 0
On Error Resume Next
myId = Evaluate(Names("UniqueId").RefersTo) + 1
On Error GoTo 0
ActiveWorkbook.Names.Add Name:="UniqueId", RefersTo:="=" & myId

End Sub

You can access this on a worksheet with

=UniqueId

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Thanks, The counter is just a simple onely ie : purchase order number,
which is incremented and moved to another sheet when saved.


any thoughts on how we can do this ?
 
Pete,

I thought that that was what my suggestion did, so I guess I am not quite
understanding your last response.

What exactly do you mean by '... which is incremented and moved to another
sheet when saved'?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

the following process is what is require

1) When the purchase order spreadsheet is saved
2) the existing purchase order number is moved to another worksheet
3) This number (in the purchase order form) is then incremented by one

example


1) proior to the save the POFN ( purchase order form number) is of
number 100

2) when saved the POFN of 100 is moved to the purchase raised work
book

3) this number of 100 in the POF is then incremented to 101

and so forth after each save. My apologise if I miss understood you
reposonse earlier but the is all rocket science to me and I truly
appreciate your assistance
 
Pete,

I'm still not fully there but let's make a few assumptions
1. When you save the POFN, you create a new one based upon that workbook
2. The new workbook gets the next number
3. The new workbook includes the number in the name
4. The new workbook isn't automatically saved

This means that we can use the number from the name rather than a workbook
name.

Does this make sense? While you ruminate and give me feedback, I will cut
some code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob

Thanks for the reply and my aplogise that I am not replied any earlie
due to personal reasons. ( father not too well)

any way

Ther is only one spreadsheet and when saved the POF deatils and ,move
to the pruchase order work book within the same file.

it the clears some rows and finally increments one to the POFN. the
this is the final thing it does.

I hope this makes sense

I have attached a spreadsheet for you to see and thank

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=39623
 
Back
Top