Copy & paste contents not cell in Excel 2

[benzgc]

I have made a macro that does a "copy paste" action. Because the "copy" is on a formula cell, the macro has to do a "paste special" function to just put the numeric value into the new cell. Now i need to copy just the numeric value into the clip board, control c is putting the cell in the clip board not the contents i just want the contents.

Any one have any ideas?

BENZ

 

[AnotherHiggins]

If you just want to capture the value of the cell,

MyValue = range("a1").value

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[benzgc]

Here is the contents of the macro.


ActiveWindow.SmallScroll Down:=-4
Range("H7").Select
Selection.Copy
Range("H8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("H8").Select
MyValue = Range("h8").Value
End Sub

I am missing something here because it is not copying anything.

Any Ideas?

BENZ

 

[combo]

Why not just, as John suggests:

Range("H8")=Range("H7").Value

???

combo

 

[AnotherHiggins]

MyValue = Range("h8").Value"
won't copy the value to the clipboard, it assigns that value to the variable MyValue. If you need to have it in the clipboard, you can just copy Range("h8").

But as long as you are in the macro you might not need to copy to the clipboard. If you want to do anything else with the value, you can just use the variable MyValue.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[benzgc]

I need to have the value copied to the clipboard so that I can paste the value into another application.

BENZ
"Non erravi perniciose!"

 

[xlbo]

bit up the creek then aren't you....

you will need to make the extra step as you cannot copy just the text in the cell.

You copy the object (the cell) and then manipulate its property (the text / the formula) - you do not copy the text

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WalterContrata]

Would it be helpful to copy the Excel Cell with all the gory details to the clipboard and then use the PasteSpecial method of the destination application to paste only the data you want?

For example,

ExlSh.Cells(1, 1).Copy    'Copy cell with formula

WrdRn.PasteSpecial DataType:=wdPasteText  'Paste as text

where ExlSh is an Excel worksheet, and WrdRn is a range in a Word Document.

The following is a demo that runs in Excel (My machine: Windows 2000, Office 2000). It requires a reference to the Word object libary. (In the VBA editor, Tools -> References -> Microsoft Word 9.0 Object Library

Sub CopyAndPasteFromExcelToWord()

Dim Exl As Excel.Application        'The source application
Dim ExlWb As Excel.Workbook
Dim ExlSh As Excel.Worksheet

Set Exl = Application
Set ExlWb = Exl.ActiveWorkbook
Set ExlSh = ExlWb.ActiveSheet

Exl.WindowState = xlNormal         'Resize the window
Exl.Top = 0                        'To see source (Excel)
Exl.Left = 0                       'and destination (Word)
Exl.Height = 300                   'simultaneously

Dim Wrd As Word.Application        'Set up the destination
Dim WrdDoc As Word.Document
Dim WrdRn As Word.Range

Set Wrd = New Word.Application
Wrd.WindowState = wdWindowStateNormal
Wrd.Top = 300
Wrd.Left = 0
Wrd.Height = 300
Wrd.Visible = True
Wrd.Activate

Set WrdDoc = Wrd.Documents.Add
Set WrdRn = WrdDoc.Range(Start:=0, End:=0)

ExlSh.Cells(1, 1).Copy                       'Copy cell with formula

WrdRn.PasteSpecial DataType:=wdPasteText     'Paste as text

End Sub

Best,
Walter

 

[WalterContrata]

Oops. Forgot to clean up.

...
Set ExlSh = Nothing
Set ExlWb = Nothing
Set Exl = Nothing

Set WrdDoc = Nothing
Set WrdRn = Nothing
Set Wrd = Nothing

End Sub

 

[combo]

If you need just a value in a cell to be copied to the clipboard, reference MSForms library and:

Dim oDataObject As MSForms.DataObject
Set oDataObject = New MSForms.DataObject
oDataObject.SetText Range("H8").Value
oDataObject.PutInClipboard
Set oDataObject = Nothing

combo

 

[WalterContrata]

combo,

That is slick.

 

[xlbo]

slicker than the proverbial off a shovel - very nice

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question