How can I copy a range of cells from one WS to another WS 2

[JohnBOA]

I have this worksheet (WS) that has many queries and formulas and it automatically starts everyday at 6AM. I need to email the users a copy of this WS but not all the queries, only the results of the query. I thought if there was a way to copy (not manually copy and paste) the original WS and a specify range of cells to another WS then I could save this worksheet and email it off to my users. Any help or thoughts would be very much appreciated.

John

 

[Scott02]

John,

Do you think the camera button would work? It maintains the link between two worksheets, so you could update on one, and send off the other without the copy and paste.

Scott

 

[JohnBOA]

I do not know what the camera button is. Could you explain it or tell me where I can find some information on it?

 

[Scott02]

You can find the camera button under the "Tools" toolbar. It looks just like a little camera. Once you have it on your toolbar, select the range you would normally copy and paste, hit the camera icon, and go to the sheet you want to paste to. Use the mouse to draw a "paste area". This will be where your image will appear. It will stay linked to the original source document so that even formatting changes will automatically update. I've found it very useful for compiling data from different sheets with different formats, into one page for someone to use. Hope this helps.

Scott

 

[JohnBOA]

Scott;
Thank you! Thank You! Thank You! Did I say Thank You?

This is exactly what I needed. Also I am adding some information on the "Camera Button" for those like myself that do not have this button.

John

Excel's Camera tool (i.e., a toolbar button with an image of a camera) is a bit elusive these days. It no longer appears on any of the built-in toolbars. So if you want to use it, you'll need to add it to a toolbar (it's in the Tools category of the Customize dialog box). It's description is as follows:

Camera: Takes a linked picture of the current selection and pastes it in a new location. The picture is linked by a formula that refers to the copied cells, so it is updated when the copied cells change. Click the Camera button to take the picture, and then click where you want to paste the upper-left corner of the selection.

Even if the Camera tool is not on a toolbar, you can get exactly the same effect by following these steps:

Select the range
Choose Edit - Copy
Press the Shift key and choose Edit - Paste Picture Link (this command is available only when the Shift key is pressed).
The result is a linked picture of the original range. You can move this picture anywhere you like.
The Camera tool is most often used to overcome Excel's problem with printing non-contiguous ranges. As you may know, when you try to print non-contiguous ranges, Excel insists on printing each area on a separate sheet of paper. You can use the Camera tool to create linked pictures of the ranges on a separate worksheet. You can then print the pictures on a single sheet of paper.

Another use for the Camera tool is to overlay a picture of a range on top of another range. In the figure below, some of the cells in columns C:F contain an "x" to indicate a missing value. Notice that column B shows a red [Enter] if any columns C:F contain an "x.". But where does the red [Enter] come from? Notice the formula bar is empty (the “[Empty]” text isn’t in the cell).

The red text actually comes from a linked picture of another range placed on top of the worksheet range. If the linked picture is formatted with no fill and no lines, it is essentially transparent, and the user can click right through it and select a cell. The only way to actually select the linked picture is to click on its (invisible) border.



Here's where it gets weird. This "click through a picture" technique no longer works in Excel 2000 or later. Clicking anywhere within the picture (even when formatted as no line and no fill) selects the picture, not the underlying cell.

But there's a solution. In order to be able to select “through” the picture, you need to save the workbook as an Excel 5.0/95 Workbook. Then reopen it, and you can select through the picture. You can even then re-save it as a normal workbook and you will still be able to select through the picture. Go figure... (contributed by Bob Umlas)

Finally, using a linked picture on an Excel 5/95 dialog sheet can be very useful. Unfortunately, it's not possible to use a linked picture on a UserForm.

 

[Scott02]

John,

Glad to help. I'm one of those Excel junkies that only knows enough to be dangerous, and I'm always excited to find a new trick that makes my job easier. Take care.

Scott