?
Solved

Replacing a range of cells with another range (of the same size)

Posted on 2014-08-27
10
Medium Priority
?
68 Views
Last Modified: 2014-11-18
I need to copy a group of cells, eg. A1 to D3, and paste them (values and formats) over an equivalent group of cells on another worksheet.

Is there any way of doing this without copying and pasting as this takes a long time given that I want to repeat this process about 30 times?

Many thanks.
0
Comment
Question by:Ben Cheetham
  • 4
  • 4
9 Comments
 

Author Comment

by:Ben Cheetham
ID: 40289098
PS.  I can define both groups of cells as 'ranges' but can I 'replace' one range with another?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40289137
You can refer to multi-cell ranges externally, but it will only display the values, not formats.  Furthermore, the cell references in the destination sheet must be the same as those in the source range.

The best method would be a VBA function to copy and paste the data.  If you're open to that solution, provide more detail of your workbook construction and what you need to do.  Sample workbooks are also helpful.

Regards,
-Glenn
0
 

Author Comment

by:Ben Cheetham
ID: 40289193
Hi Glenn

Many thanks for such swift input.Characters
Rather than send you the whole workbook (messy and confusing), I've attached a picture of what-I'll-refer-to-as Sheet1.  Sheet 2 - the destination sheet is the same, but the 'characters' might not be in the same location.  (For what it is worth, in the real version, the characters are students and the whole workbook relates to school seating plans.)

My macro currently works through each character row by row and locates on sheet2 each person on sheet1.  Effectively, it then copies that person and their data from sheet1 and pastes it over the record on sheet2 to reflect any updates to their data.  However, with up to 40 records to find and then copy/paste, it takes more time than I'd like it to.

Have you any better ideas?!

Many thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ben Cheetham
ID: 40289194
BTW: Terry Wogan should only appear once!  Names on each sheet are unique.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40289660
Am I correct in restating the requirements like so?

1) You have Sheet1 which has a collection of 4x4 cells (1 merged, 2x4 individual) that identify a person and eight possible values which are also formatted (font & color).
2) You have Sheet2 which is laid out exactly like Sheet1, but only has the merged cells filled in with persons' names.

You want a method that will populate the related values & formats for each person that are on Sheet1 onto Sheet2.  When done, Sheet2 groups should appear identical to those in Sheet1, just in new positions.

=========================================================================
My two immediate impressions are this:
1) The first sheet should be a data table that shows one row/record per person with the eight values to the right, like so:
data table - Sheet1This would make it a true database that could be referenced on Sheet2 with all related fields.  Of course, I don't know what each box actually represents, but the field labels could be changed accordingly.

2) The second sheet could use indexing/lookup functions to properly populate the groups of cells based on this data.  Then, instead of hard-formatting each cell, you could use conditional formatting to show the desired font and cell color for the values.

Just for grins, I went ahead and created an example workbook that uses these two methods.  While I don't have the field names for the table, you could fill that in.  But Sheet2 has all the conditional formatting rules to replicate the appearance of the cells in your example.  And, each merged name block uses data validation on the data table so you can pick names from a drop list (or type them, if preferred).

Regards,
-Glenn
EE-Q28506802.xlsx
0
 

Author Comment

by:Ben Cheetham
ID: 40289761
Dear Glenn

Many thanks for such excellent deductions and the advice.

I didn't include all the detail to this workbook because it is so fiddly and amateurish.  I am little more than a novice with VBA.  But, sheet1 is formed from a data table like the one you've drafted.  It has data validation on it.  The intention is then to copy and paste as values/formats to sheet2 because the version on sheet2 is then copied and pasted to a third sheet as little 4 x 4 pictures that can be moved around like tiles on a board, hence we have an adjustable seating plan for a classroom of students.  We can then easily position a student on their academic achievement, whether or not English is their first language, etc.

It would be too cheeky for me to send through the whole spreadsheet as it would be a project in itself and I don't think that's not really the purpose of this forum.  It wouldn't be fair!  Plus, an expert like you would be questioning why I've done things as I have; sometimes there's good reason, sometimes it is naivety!

I will happily accept your solution as it is as you've put in more than enough time and effort to help.  I am sorry that I've not presented the whole problem in one go - I just felt it would be asking too much.

Kindest regards and thanks.
Ben
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40301470
Ben,

You may want to consider using Excel's Camera option to copy and paste virtual images of data in to your third sheet.  This will allow you to have dynamically updated graphics that are tied to the source image data on Sheet2 while allowing you to freely move them about as you request.

The Camera option is a little-documented feature of Excel and you have to add it to a toolbar in order to use it.  I have added one to my Quick Access toolbar:
camera icon - QA toolbar
To do this:
1) Right-click on the ribbon or quick-access toolbar and select "Customize Quick Access Toolbar..." from the shortcut menu
2) In "Choose commands from:" select "All commands".
3) Scroll down the list and find "Camera".  Click the "Add>>" button.
4) Click the "OK" button.
selection menu
To use, just highlight any cells you want to copy as an image, then click the camera button.  Navigate to the new sheet and click on any cell.  A copy of the selected cells will appear as a picture that you can format (resize, rotate, flip).  The image data will update with the source cells.

I've attached a modified version of my sample file that demonstrates this.

Regards,
-Glenn

Resources:
Chandoo.org
Addictive Tips
EE-Q28506802.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40323660
Hi,

Even though it appears that you're not sure about requesting more assistance, could you please properly close this question by clicking the "Accept this solution" link above my submission above that best answers your question?  This will help ensure that future searches are meaningful to other EE members.

Otherwise, let us know if you have any other issues.

Thanks,
 -Glenn
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40449340
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question