exact copy/paste
 

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" t
"$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 ho
I see it:

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

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

I could manage (see code below) to get this done via input boxes, bu
I would like to replace the input selection by taking copied cell
directly from the clipboard, and to replace the output selection b
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
Nex

--
jmwisme
-----------------------------------------------------------------------
jmwismer's Profile: http://www.excelforum.com/member.php...fo&userid=2820
View this thread: http://www.excelforum.com/showthread.php?threadid=47742

exact copy/paste
 

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" wrote in
message ...

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

exact copy/paste
 

Thank you Norman.

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

thx,
j

--
jmwisme
-----------------------------------------------------------------------
jmwismer's Profile: http://www.excelforum.com/member.php...fo&userid=2820
View this thread: http://www.excelforum.com/showthread.php?threadid=47742

exact copy/paste
 

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" wrote in
message ...

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

exact copy/paste
 

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" wrote in
message ...

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

exact copy/paste
 

Wow, thank you Norman!

works on a single cell though. Can I use my initial code somehow to th
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,
j

--
jmwisme
-----------------------------------------------------------------------
jmwismer's Profile: http://www.excelforum.com/member.php...fo&userid=2820
View this thread: http://www.excelforum.com/showthread.php?threadid=47742

exact copy/paste
 

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" wrote in
message ...

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