Microsoft Excel - Copying and linking worksheets within same workbook

Asked By ams clarks on 12-Nov-08 04:59 PM

Hi everyone

 I really need help in trying to do the following:- Create a linked copy of a worksheet in the same workbook. The worksheets can be linked by using the formula "=Sheet1!A1". However, it is very tedious (as the worksheet I am trying to copy has hundreds of columns), and shows "0" when the cell is empty. Also, I need to do it on a monthly basis.

Is it possible to write a macro such that when you click on a button, a linked copy of a worksheet is created within same workbook.

Linking is necessary because if a mathematical change is made in a previous worksheet, the change can be reflected in the copied worksheet.


Help is highly appreciated


Thanks


VBA beginner

re - Web Star replied to ams clarks on 12-Nov-08 11:27 PM

Create a link between cells in the same worksheet or workbook

  1. Click the cell that contains the data you want to link to, and then click Copy on the Standard toolbar.
  2. Click the cell you want to link from, and then click Paste.
  3. Click Paste Options and then click Link Cells.

Create a link between cells in different workbooks

  1. Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the source workbook).
  2. In the destination workbook, click Save.
  3. Select a cell or cells you want to link from.
  4. If you are creating a new formula, type = (an equal sign).

If you are entering the link elsewhere in the formula, type the operator or function that you want to precede the link.

5.      On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to.

  1. Select the cells you want to link to.
  2. Complete the formula. When you finish entering the formula, press ENTER

http://myabacus.wordpress.com/2008/06/12/excel-toolbox-linking-worksheets-workbooks-and-embeds/

Copying Worksheets in a Macro - Binny ch replied to ams clarks on 13-Nov-08 02:42 AM

The Copy method, when used with worksheets, is not limited to copying a single sheet at a time. If you have a group of sheets selected, you can still use a single command line to copy all of them to a new workbook. That is what is done in the following macro:

Sub CopyWorkbook()
Dim sCopyName As String

sCopyName = "My New Workbook.xls"

ActiveWindow.SelectedSheets.Copy
ActiveWorkbook.SaveAs Filename:=sCopyName, _
FileFormat:=xlNormal
End Sub

Note the use of the Copy command. The macro will work whether you have one worksheet selected or fifty; it doesn't matter. If you wanted to, instead, copy all of the worksheets from one workbook to another, all you need to do is make a single change in the macro, to the line where the Copy method is invoked:

    Sheets.Copy

This copies the entire Sheets collection, which consists of all the worksheets in the workbook.

It should be noted that the Copy method isn't just for copying worksheets to a new workbook; it can also be used to copy worksheets within the same workbook. The only thing you need to do is specify where in the current workbook you want to make the copy:

ActiveSheet.Copy After:=Sheets("Sheet7")

This code line copies the active worksheet into the same workbook so that it appears after the worksheet named Sheet7. If it is more appropriate for your needs, you could instead specify the worksheet before which the copy should be placed:

ActiveSheet.Copy Before:=Sheets("Sheet7")


Also see this link:
http://excel.tips.net/Pages/T002784_Copying_Worksheets_in_a_Macro.html

read this - C_A P replied to ams clarks on 13-Nov-08 06:48 AM

Create a link between cells in the same worksheet or workbook

  1. Click the cell that contains the data you want to link to, and then click Copy on the Standard toolbar.
  2. Click the cell you want to link from, and then click Paste.
  3. Click Paste Options and then click Link Cells.

Create a link between cells in different workbooks

  1. Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the source workbook).
  2. In the destination workbook, click Save.
  3. Select a cell or cells you want to link from.
  4. If you are creating a new formula, type = (an equal sign).

If you are entering the link elsewhere in the formula, type the operator or function that you want to precede the link.

5.      On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to.

  1. Select the cells you want to link to.
  2. Complete the formula. When you finish entering the formula, press ENTER.

Link or embed content from another program using Object Linking and Embedding (OLE)

OLE (Object Linking and Embedding) is supported by many different programs, and is used to make content created in one program available in another. For example, you can insert a Microsoft Word document into Microsoft Excel. To see what types of content you can insert, click Object on the Insert menu. Only programs that are installed on your computer and that support OLE  objects appear in the Object type box.

The main differences between linked objects and embedded objects are where the data is stored and how the object is updated after you place it in the destination file.

Embedded objects are stored in the workbook they are inserted in, and are not updated. Linked objects remain as separate files, and need to be updated.

Linked and embedded objects in a document

Create a link to an existing document

1.      Click in the worksheet where you want to place the linked object.

2.      On the Insert menu, click Object.

3.      Click the Create from File tab.

4.      In the File name box, type the name of the file, or click Browse to select from a list.

5.      Select the Link to file check box.

6.      To display the content, clear the Display as icon check box.

If you want to display as an icon, select the Display as icon check box.

Note You cannot use the Object command on the Insert menu to insert graphics and certain types of files. To insert a graphic, point to Picture on the Insert menu, and then click From File.

Embed a document

1.      Click in the worksheet where you want to place the embedded object.

2.      On the Insert menu, click Object.

3.      If the document does not already exist, click the Create New tab. In the Object type box, click the type of object you want to create.

If the document already exists, click the Create from File tab. In the File name box, type the name of the file, or click Browse to select from a list.

4.      Clear the Link to file check box.

5.      To display the content, clear the Display as icon check box.

To display as an icon, select the Display as icon check box.

Insert part of an existing document as a linked or embedded object

1.      From a program other than Microsoft Excel, select the information you want to copy as a linked or embedded object.

2.      Click Copy.

3.      Switch to the worksheet you want to place the information in, and then click where you want the information to appear.

4.      On the Edit menu, click Paste Special.

5.      To paste the information as a linked object, click Paste link.

To paste the information as an embedded object, click Paste. In the As box, click the entry with the word “object” in its name. For example, if you copied the information from a Microsoft Word document, click Microsoft Word Document Object.

Note You can’t paste worksheet cells or a chart as a linked or embedded object in a Microsoft Excel workbook. However, you can use the Copy Picture command (hold down SHIFT and click the Edit menu) to create a link to cells on this or another sheet

Create a reference between cells on the same or different worksheets

The following formulas contain relative reference to and name of other cells. The cell that contains the formula is known as a dependent cell when its value depends on the values in other cells. For example, cell B2 is a dependent cell if it contains the formula =C2.

Example formula

What it does

=C2 Uses the value in the cell C2
=Sheet2!B2 Uses the value in cell B2 on Sheet2
=Asset-Liability Subtracts a cell named Liability from a cell named Asset
  1. Click the cell in which you want to enter the formula.
  2. In the formula bar type = (equal sign).
  3. Do one of the following:
    • To create a reference, select a cell, a range of cells, a location in another worksheet, or a location in another workbook. You can drag the border of the cell selection to move the selection, or drag the corner of the border to expand the selection.
    • To create a reference to a named range, press F3, select the name in the Paste name box, and click OK.
  4. Press ENTER.

Link to a name in another workbook

You can create descriptive names to represent a cell or range of cells in another workbook.

Link to a defined name in another workbook

1.      Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the source workbook).

2.      In the destination workbook, click Save.

3.      Select a cell or cells you want to link from. If you are creating a new formula, type = (an equal sign).

If you are entering the link elsewhere in the formula, type the operator or function that you want to precede the link.

4.      On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to.

5.      Press F3, and select the name you want to link to.

Define a name that refers to cells in another workbook.

Hopefully this helps point you in the direction you would like to go regarding links on worksheets.  I will try to be back full strength next week.

read this ebook - C_A P replied to ams clarks on 13-Nov-08 06:50 AM
http://www.sienaheights.edu/Personal/csstrain/Excel/Copy-Embed-Link.pdf
http://www.mvps.org/dmcritchie/excel/sheets.htm
same workbook - ams clarks replied to Binny ch on 13-Nov-08 03:03 PM

Hi Binny

The code works great if I am copying the worksheet to a different workbook, however, I am trying to paste the worksheet within "same" workbook. Is it possible to modify the code so that it pastes the worksheet in same workbook?

Also, what I was trying to achieve is link the copied worksheet to the original one, and later on rename the copied on. For example, we create a copy of worksheet "May" . Now I rename this "May" copy to "June". However, if the value in cell A1 was wrong for "May", we change it , and the change is also reflected in "June"