exact copy/paste

[jmwismer]

Hi there,

I often need to perform an "exact copy-paste", i.e.

=$a$1+b1

gets copied two lines below as

=$a$1 + b1

and not as

=$a$1 + b3

I obviously would like this done without having to change "b1" to "$b$1". Since I could not find any tips or functions to get this done, I suppose that I will have to write a VB macro to do that. Here is how I see it:

1. I select a range of cells, and do a CTRL-C to get those in the clip board
2. I select a traget cell
3. I do a, say, CTRL-SHF-V to perform my exact copy to the new location.

My VB experience is close to zero, and OO competence is absolutely zero.

I could manage (see code below) to get this done via input boxes, but I would like to replace the input selection by taking copied cells directly from the clipboard, and to replace the output selection by getting location of active cell and pasting from there.

Can anyone help?

thx a lot,
jm


Dim mycells1, mycells2 As Range
Dim cells As Range
Dim i, cellules, cell(100)
Set mycells1 = Application.InputBox(prompt:="Input range.", _
Title:="Exact Copy-Paste", Left:=500, Top:=300, Type:=8)
i = 1
For Each cellules In mycells1
cell(i) = cellules.Value
i = i + 1
Next

i = 1
Set mycells2 = Application.InputBox(prompt:="Output range.", _
Title:="Exact Copy-Paste", Left:=500, Top:=300, Type:=8)

For Each cellules In mycells2
cellules.Value = cell(i)
i = i + 1
Next

[Norman Jones]

Hi JM,

See Chip Pearson's ClipBoard page at:

http://www.cpearson.com/excel/clipboar.htm

See particularly, the CopyFormula and PasteFormula macros at the foot of the
page and note Chip's right-click menu suggestion.

Note that to use these macros, you need to set a reference in the VBE to the
Microsoft Forms 2.0 object library..


---
Regards,
Norman



"jmwismer" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I often need to perform an "exact copy-paste", i.e.
>
> =$a$1+b1
>
> gets copied two lines below as
>
> =$a$1 + b1
>
> and not as
>
> =$a$1 + b3
>
> I obviously would like this done without having to change "b1" to
> "$b$1". Since I could not find any tips or functions to get this done,
> I suppose that I will have to write a VB macro to do that. Here is how
> I see it:
>
> 1. I select a range of cells, and do a CTRL-C to get those in the clip
> board
> 2. I select a traget cell
> 3. I do a, say, CTRL-SHF-V to perform my exact copy to the new
> location.
>
> My VB experience is close to zero, and OO competence is absolutely
> zero.
>
> I could manage (see code below) to get this done via input boxes, but
> I would like to replace the input selection by taking copied cells
> directly from the clipboard, and to replace the output selection by
> getting location of active cell and pasting from there.
>
> Can anyone help?
>
> thx a lot,
> jm
>
>
> Dim mycells1, mycells2 As Range
> Dim cells As Range
> Dim i, cellules, cell(100)
> Set mycells1 = Application.InputBox(prompt:="Input range.", _
> Title:="Exact Copy-Paste", Left:=500, Top:=300, Type:=8)
> i = 1
> For Each cellules In mycells1
> cell(i) = cellules.Value
> i = i + 1
> Next
>
> i = 1
> Set mycells2 = Application.InputBox(prompt:="Output range.", _
> Title:="Exact Copy-Paste", Left:=500, Top:=300, Type:=8)
>
> For Each cellules In mycells2
> cellules.Value = cell(i)
> i = i + 1
> Next
>
>
> --
> jmwismer
> ------------------------------------------------------------------------
> jmwismer's Profile:
> http://www.excelforum.com/member.php...o&userid=28201
> View this thread: http://www.excelforum.com/showthread...hreadid=477424
>

[jmwismer]

Thank you Norman.

I am of no use for OO programming. Any clue where I can find some help on how to transfer my cells form an object such as MyDataObj (which gets the clipboard content) to an array cell(i)?

thx,
jm

[Gary Keramidas]

if you had 10 in a1 and 20 in b2 and =$a$1+b1 in c1, this would put the
exact formula in c3

Range("c3").Formula = Range("c1").Formula

if there was some semblance of order to your formula, we could write a loop
of some kind to fill a range.

--


Gary


"jmwismer" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you Norman.
>
> I am of no use for OO programming. Any clue where I can find some help
> on how to transfer my cells form an object such as MyDataObj (which
> gets the clipboard content) to an array cell(i)?
>
> thx,
> jm
>
>
> --
> jmwismer
> ------------------------------------------------------------------------
> jmwismer's Profile:
> http://www.excelforum.com/member.php...o&userid=28201
> View this thread: http://www.excelforum.com/showthread...hreadid=477424
>

[Norman Jones]

Hi JM,

Alt-F11 to open the VBE

Tools | References | Scroll to 'Microsoft Forms 2.0 Object Libary'
Check this library entry | OK
Menus | Insert | Module
Copy / Paste the following code:

'===================>>
Sub CopyFormula()
Dim x As New DataObject
x.SetText ActiveCell.Formula
x.PutInClipboard
End Sub

'--------------

Sub PasteFormula()
On Error Resume Next
Dim x As New DataObject
x.GetFromClipboard
ActiveCell.Formula = x.GetText
End Sub

'--------------

Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant
onaction_names = Array("CopyFormula", "PasteFormula")
caption_names = Array("Copy Fixed Formula", _
"Paste Fixed Formula")

With Application.CommandBars("Cell")
For i = LBound(onaction_names) To _
UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name _
& "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i
End With
End Sub

'--------------

Sub Delete_Controls()
Dim i As Long
Dim caption_names As Variant
caption_names = Array("Copy Fixed Formula", _
"Paste Fixed Formula")
With Application.CommandBars("Cell")
For i = LBound(caption_names) To _
UBound(caption_names)
On Error Resume Next
.Controls(caption_names(i)).Delete
On Error GoTo 0
Next i
End With
End Sub
'===================>>

Place the cursor anywhere inside the Add_Controls macro, press the F5
function key.

Alt-F11 to return to Excel.

Now right-click any cell. The resultant menu will have two new commands,
namely: 'Copy Fixed Formula' and 'Paste Fixed Formula'

The Delete_Controls macro is added to enable you to delete the two new menu
items, should you choose to do so.


---
Regards,
Norman


"jmwismer" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you Norman.
>
> I am of no use for OO programming. Any clue where I can find some help
> on how to transfer my cells form an object such as MyDataObj (which
> gets the clipboard content) to an array cell(i)?
>
> thx,
> jm
>
>
> --
> jmwismer
> ------------------------------------------------------------------------
> jmwismer's Profile:
> http://www.excelforum.com/member.php...o&userid=28201
> View this thread: http://www.excelforum.com/showthread...hreadid=477424
>

[jmwismer]

Wow, thank you Norman!

works on a single cell though. Can I use my initial code somehow to the data object to loop and do the job for a range of selected cells?

thx a lot too for the right-click menu tip!

best regards,
jm

[Gary Keramidas]

this would put the exact formula from whatever column range you enter, and
put it in the next column. maybe you can adapt from here

Sub test()


Dim mycells1, mycells2 As Range
Dim cells As Range
'Dim i, cellules, cell(100)
Set mycells1 = Application.InputBox(prompt:="Input range.", _
Title:="Exact Copy-Paste", Left:=500, Top:=300, Type:=8)
i = 1
Debug.Print mycells1.Address
For Each cell In Range(mycells1.Address)
'
cell.Offset(0, 1).Formula = cell.Formula
i = i + 1
Next
End Sub

--


Gary


"jmwismer" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I often need to perform an "exact copy-paste", i.e.
>
> =$a$1+b1
>
> gets copied two lines below as
>
> =$a$1 + b1
>
> and not as
>
> =$a$1 + b3
>
> I obviously would like this done without having to change "b1" to
> "$b$1". Since I could not find any tips or functions to get this done,
> I suppose that I will have to write a VB macro to do that. Here is how
> I see it:
>
> 1. I select a range of cells, and do a CTRL-C to get those in the clip
> board
> 2. I select a traget cell
> 3. I do a, say, CTRL-SHF-V to perform my exact copy to the new
> location.
>
> My VB experience is close to zero, and OO competence is absolutely
> zero.
>
> I could manage (see code below) to get this done via input boxes, but
> I would like to replace the input selection by taking copied cells
> directly from the clipboard, and to replace the output selection by
> getting location of active cell and pasting from there.
>
> Can anyone help?
>
> thx a lot,
> jm
>
>
> Dim mycells1, mycells2 As Range
> Dim cells As Range
> Dim i, cellules, cell(100)
> Set mycells1 = Application.InputBox(prompt:="Input range.", _
> Title:="Exact Copy-Paste", Left:=500, Top:=300, Type:=8)
> i = 1
> For Each cellules In mycells1
> cell(i) = cellules.Value
> i = i + 1
> Next
>
> i = 1
> Set mycells2 = Application.InputBox(prompt:="Output range.", _
> Title:="Exact Copy-Paste", Left:=500, Top:=300, Type:=8)
>
> For Each cellules In mycells2
> cellules.Value = cell(i)
> i = i + 1
> Next
>
>
> --
> jmwismer
> ------------------------------------------------------------------------
> jmwismer's Profile:
> http://www.excelforum.com/member.php...o&userid=28201
> View this thread: http://www.excelforum.com/showthread...hreadid=477424
>