Menu

how do I paste without EXCEL increasing the cell number ?

Search this Thread

Prev

Previous Post

Next Post

Next

#6

Report Post

Posted to microsoft.public.excel.worksheet.functions

Harlan Grove

Posts: n/a

Default

how do I paste without EXCEL increasing the cell number ?

wrote...
....
In cases where I actually want to __copy__ and paste, I
have resorted to a text copy-and-paste. This is unpleasant
because it must be done one cell at a time. Highlight one
cell and copy the text of the formula in the "fx" field.
Press Esc to deselect the cell, then highlight the new
location, then do paste.

If the formulas to be copied exactly were in a single area range,
easier to select that range, Edit Replace = with |=, copy then paste
elsewhere, then select both copied and pasted ranges and Edit Replace
|= with =. If that still too unpleasant, use a macro.

Sub foo()
Dim r As Range, ac As Variant

If Not TypeOf Selection Is Range Then Exit Sub

On Error Resume Next

Set r = Application.InputBox( _
Prompt:="Select paste range", _
Title:="Formula Copy", _
Type:=8 _
)

If Err.Number < 0 Or r Is Nothing Then
Err.Clear
Exit Sub
ElseIf r.Cells.Count < Selection.Cells.Count Then
Set r = r.Cells(1).Resize(Selection.Rows.Count,
Selection.Columns.Count)
End If

On Error GoTo CleanUp

ac = Application.Calculation
Application.Calculation = xlCalculationManual

Selection.Replace What:="=", Replacement:="|=", lookat:=xlPart
Selection.Copy Destination:=r
Union(Selection, r).Replace What:="|=", Replacement:="=",
lookat:=xlPart

CleanUp:
Application.Calculation = ac

End Sub

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ???	Richard	Excel Discussion (Misc queries)	2	May 13th 23 11:46 AM
Instead of a negative number, I'd like to show zero...	Dr. Darrell	Excel Worksheet Functions	6	December 7th 05 08:21 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER	MEGTOM	New Users to Excel	5	October 27th 05 03:06 AM
Possible Lookup Table	Karen	Excel Worksheet Functions	5	June 8th 05 09:43 PM
Can an Excel formula be created to paste a picture in a cell?	bonita0914	Excel Worksheet Functions	2	April 28th 05 06:11 PM

All times are GMT +1. The time now is 08:15 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement