Solved

EXCEL - COPY WORKSHEET FORMATS, VALUES, TEXT BOXES

Posted on 2011-02-18
6
908 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

867 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now