Solved

EXCEL - COPY WORKSHEET FORMATS, VALUES, TEXT BOXES

Posted on 2011-02-18
6
911 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:Frank .S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Frank .S
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
Technology Partners: 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:Frank .S
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:Frank .S
ID: 34931207
thankyou so much teylyn
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

733 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