Solved

EXCEL - COPY WORKSHEET FORMATS, VALUES, TEXT BOXES

Posted on 2011-02-18
6
910 Views
Last Modified: 2012-05-11
I want to copy a worksheet from 1 excel workbook into another workbook but when i try to do this the values and formats copy over but the text boxes have not copied over, please assist.
0
Comment
Question by:FrankSasso
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34931049
Hello,

how are you copying? If you right-click the sheet tab, select Move or Copy, then select a workbook in the top drop down, tick "Create a copy", the whole worksheet will be copied, text boxes and all.

cheers, teylyn
0
 

Author Comment

by:FrankSasso
ID: 34931059
hi teylyn, sorry should have provided more information. What has happened, is that I can only copy and paste into the other workbook because the worksheet in the other workbook has code which is working in the other workbook i'm wanting to copy to, so i need to copy the information only into a particular worksheet with existing code, i hope i have explained a little more clearly for you.
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 125 total points
ID: 34931093
Hello,

if you can't copy the whole sheet, then you'll need to do this in two steps. First copy the cells with values and formats. Then go back to the source sheet and hit F5, click Special, select Objects and hit OK. Now all objects like text boxes, images, etc are selected. Use your favourite Copy command and paste into the target sheet.

Tip: When you paste the objects, they may appear out of place, since Excel pastes them to the left and top most position. In order to preserve the absolute location of the text boxes, create a new, helper text box in the source sheet on top of cell A1. Then select all objects, copy and paste to the target sheet. The top left text box will sit on top of A1 again and the other text boxes will be placed correctly, too. After that, select only the text box at A1 and delete it.

cheers, teylyn
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:FrankSasso
ID: 34931155
hi teylyn, for some reason now it wont allow me to copy the old worksheet to the new one, i dont get the standard 'paste special' window, i get this other one and i dont know what to do, please see my posted screenshot and let me know what i need to do to get the std 'paste special' window again. paste special window
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 125 total points
ID: 34931165
This happens when the two workbooks are open in different instances of Excel.

Close the target workbook. Go to the source workbook and use File - Open to open the target workbook. Now they are in the same Excel instance and the Paste Special will show the options you need.

(this is really a different question and should have been posted separately)

cheers, teylyn
0
 

Author Closing Comment

by:FrankSasso
ID: 34931207
thankyou so much teylyn
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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