Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

EXCEL - COPY WORKSHEET FORMATS, VALUES, TEXT BOXES

Posted on 2011-02-18
6
Medium Priority
?
916 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
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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 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
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: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 (Microsoft MVP / EE MVE) earned 500 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

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!

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

647 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