• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to paste exact cell contents ?

W

wouterstemerdink

New Member
Hi Chandoo and posters,


I am a business analyst and build business case spreadsheets. Many times I wish to duplicate a block of cells on the same sheet, while keeping all cell contents exactly the same, whether it be formulas or numbers doesn't matter. So references to other cells should stay untouched.

One way to do it would be to select to block, find/replace "=" by e.g. "#####=", then copy and paste on the destination cells, then find/replace "#####=" by "=" in both the orignal block and the destination block.

There probably is an even simpler way to do this, but I'm not that comfortable with VBA script.

-> Does anyone know how to program this to be put in a macro ?

It could then be added to the "17 clever ways to copy" on Chandoo's site.


Thanks a lot,

Wouter
 
Hi ,


You might have gone through all of these links , but just in case ...


1. http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/


2. http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2/


3. http://www.techrepublic.com/forum/discussions/102-203545?tag=discussion-asset-thread


Narayan
 
Hi Narayan,


Thank you for your swift reply. The links provide some different ways how to solve this, but all involve several steps. I would like to have this automated in VBA script.


So the sequence would be:

1- select region to copy (e.g. 5x10 cells)

2- Ctrl-C to copy

3- select top left cell of destination region (then also 5x10 cells)

4- run macro with VBA script

The result is that the destination region is EXACTLY the same as the source region.


Would anyone have some script for this ?


Thank you, make my day please !


Wouter
 
I find it appalling that MS has not added a Replicate function to the Paste Menu to do what you require.

Apple Works had that functionality in 1984! nearly 3 decades ago.
 
Hi Wouter ,


The third link given in my earlier post had the following :


QUOTE


Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1. By "exact," I mean a perfect replica -- the original cell references should not change.


If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.


If you're a VBA programmer, you can simply execute the following code:


With Sheets("Sheet1")

.Range("A11:D20").Formula = .Range("A1:D10").Formula

End With


UNQUOTE


Doesn't the code given in this help you ? Do you want the complete procedure to be posted ?


Narayan
 
Hi Wouter ,


Try this :

[pre]
Code: 
Public Sub make_an_exact_copy()

Dim copy_from_range As Range
Dim paste_to_range As Range
Dim first_cell As Range

Do
Set copy_from_range = Application.InputBox("Enter the range from which you want to copy : ", Type:=8)
Set paste_to_range = Application.InputBox("Enter the range where  you want to paste : ", Type:=8)
copy_cells = copy_from_range.Count
paste_cells = paste_to_range.Count
If copy_cells <> paste_cells Then
user_input = MsgBox("Number of cells in copy/paste selections do not match ! Cannot proceed ! Do you wish to retry ? ", vbYesNo)
If user_input <> vbYes Then GoTo Sub_Exit
End If
Loop Until copy_cells = paste_cells

Application.ScreenUpdating = False

paste_rows = paste_to_range.Rows.Count
paste_columns = paste_to_range.Columns.Count

Set first_cell = paste_to_range.Cells(1, 1)

row_counter = 0
column_counter = 0

For Each cell In copy_from_range
first_cell.Offset(row_counter, column_counter) = cell.Formula
column_counter = column_counter + 1
If column_counter >= paste_columns Then
row_counter = row_counter + 1
column_counter = 0
End If
Next

Sub_Exit:
Set copy_from_range = Nothing
Set paste_to_range = Nothing
Set first_cell = Nothing
Application.ScreenUpdating = True
End Sub
[/pre]

Narayan
 
Thanks Narayan you did it !!!

It works like a charm and fulfills my needs. I saw your previous post today and knew this is where my knowledge of VBA script is way too limited. I understand it when someone else writes it but that's not good enough. I've put the script in my PERSONAL.XLSX and attached a shortcut, so ready to rock & roll!


You programmed the input boxes in the start. Would it still be possible to simplify that further by:

- defining the copy_from_range by the range entered by the the Ctrl+C command;

- defining the paste_to_range as a range of similar size starting at first_cell ?


The targeted sequence I wrote about before was:

1- select region to copy (e.g. 5x10 cells)

2- Ctrl-C to copy

3- select top left cell of destination region (then also 5x10 cells)

4- run macro with VBA script

The result is that the destination region is EXACTLY the same as the source region.


Could this be achieved by a small adaptation ?


Thanks again to your help Narayan, your help is very much appreciated. You could "tell" Microsoft to include this in the Paste Special menu :-)


Best regards,

Wouter
 
Hi, wouterstemerdink!

You can play with SelectionChange events.

After 2), in Selection.Address you have a string with the $B$6:$K$10 value (5x10 cells, starting at B6).

After 3), same for top left cell of target range.

You'll have to split NARAYANK991 proposed code into two parts accordingly to the code of the two SelectionChange events.

Regards!
 
Hi Wouter ,


Sorry , but I think I cannot do what you want to be done !


I have been going through whatever is on the Internet , and what I have learnt is :


1. When you do CTRL C , data is put on to the Clipboard , and within Excel VBA , there are very few commands available to get data from the Clipboard ; what is available is to get data in text format.


2. Under normal conditions , even though you have formulae in cells , when data is put into the Clipboard , reading it back retrieves only the results of the formulae and not the formulae themselves.


3. If you display the formulae instead of their values in cells , and then do CTRL C, then it is possible to retrieve the formulae from the Clipboard ; would you be willing to do this ?


4. Instead of doing CTRL C , and sending data to the Clipboard , if you only select the copy-from range and copy-to range , things become much simpler.


Narayan
 
Hi Narayan,


Thank you very much again for putting in your time, I really appreciate it.

It's a pity that the access to and contents of the Clipboard is so limited, so I guess we'll have to live with that. However, in a normal Ctrl-C / Ctrl-V sequence, the data from the Clipboard that gets pasted does contain formulas but with the problematic shifted cell references etc.


First displaying formulae (Ctrl-`) (your suggestion 3) seems a bit awkward since the column sizes are increased greatly and the overview is hampered.

So I guess we'll stick with the script you wrote, that works !


-> One last question though: since the "paste_to_range" by my definition has the same size as the "copy_from_range", could you eliminate the input box for the paste range, and just start pasting at first_cell ?


Thank you,

Wouter


PS I looked up where you live, it's a great idea that we're working together in this connected world although we're in different continents! Best regards from Amsterdam!
 
Hi Wouter ,


The following code will work for the "paste to" range being just one cell :

[pre]
Code: 
Public Sub make_an_exact_copy()

Dim copy_from_range As Range
Dim paste_to_range As Range
Dim first_cell As Range

Set copy_from_range = Application.InputBox("Enter the range from which you want to copy : ", Type:=8)
Set paste_to_range = Application.InputBox("Enter the range where  you want to paste : ", Type:=8)

Application.ScreenUpdating = False

paste_columns = copy_from_range.Columns.Count

row_counter = 0
column_counter = 0

For Each cell In copy_from_range
paste_to_range.Offset(row_counter, column_counter) = cell.Formula
column_counter = column_counter + 1
If column_counter >= paste_columns Then
row_counter = row_counter + 1
column_counter = 0
End If
Next

Sub_Exit:
Set copy_from_range = Nothing
Set paste_to_range = Nothing

Application.ScreenUpdating = True
End Sub
[/pre]

Thanks for your appreciation. Best regards to you too.


Narayan
 
Thanks Narayan, this is great, now it works cleaner. I will distribute this to my colleagues with your compliments.

I also want to send this to Chandoo to include in the page "17 clever ways to copy" on this website with your name on it, is that ok for you ? Or feel free to do this yourself ofcourse.


Best regards,

Wouter
 
Hi Wouter ,


Glad your problem has been resolved. Feel free to distribute whatever you find from me. I am sure it can be improved on.


Narayan
 
You must log in or register to reply here.
Back
Top