Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Mirror a tab (Any version)

    Hi

    I'm pretty sure I know the answer ("no, you can't do it"), but here's what I'd like to do. Spreadsheet #1 contains five tabs (say A, B, C, D, and E). I want spreadsheet #2 to contain a tab that completely mirrors tab B from #1.

    It's easy enough to mirror the cells that were populated at the time #2 was created (select the cells, copy, paste special using the PasteLink button), but I want to mirror ALL cells (A1:IV65536). In other words, I want to mirror the TAB, rather than the individual cells.

    Selecting cells A1:IV65536 and using the PasteLink button isn't practical because of memory constraints. Is there any (practical) way of doing this?

    Thanks
    Dale

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mirror a tab (Any version)

    I'm sure that "yes, you can do it", but the best way would depend on how the data you wish to mirror is structured. If it is a contiguous list or table, you could build a query into the destination worksheet that would automatically update when the holding workbook is opened. If that approach is appropriate, you could have some code run automatically when the workbook is opened that would copy the used range you wish to mirror and paste it into the destination sheet.

    If you need any assistance with either method, post back.

    Andrew C

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mirror a tab (Any version)

    Could you elaborate on what you are after?

    Are you trying to update spreadsheet2 whenever spreadsheet 1 updates?

    You could have a macro (whenever a change is made to SheetB of workbook#1 is made), that it copies it to and replaces the appropriate sheet in Workbooks. The macro would open it (if not opened) replace the sheet and save it.

    This will not be too memory intensive since you will not have formulas, you will have the values.It will be "semi-live" since the macro will update the changes, though this could make the performace of Workbook#1 sluggish.
    Is this what you are after? Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mirror a tab (Any version)

    Your way is better than my approach. Using a Sheet activate rather than my sheet change will be alot less sluggish.

    It could be done with code in workbook2, particular sheet activate. Whenever the sheet with the "links" is activated, it would get an update of Workbook#1 sheetB.

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mirror a tab (Any version)

    The VBA code below, placed in the worksheet change event routine for a worksheet will mirror all changes to that sheet in Sheet2 of the same workbook. Your question was not clear since you talked about Spreadsheet#1 and Spreadsheet#2 but it sounded like you really meant workbook#1 and Workbook#2. If the sheets are in different workbooks, the code will need to be modified accordingly.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    For Each oCell In Target
    Worksheets("Sheet2").Range(oCell.Address(False, False)).Value = oCell.Value
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mirror a tab (Any version)

    Thanks for all the suggestions.

    For some reason I didn't think of using macros. I guess I was hoping that a miracle might have occurred with Mr Gates providing us with a simple linkage that gave effect to "whatever's over there, display it here".

    But you've given me lots of ideas. In this particular case I think I'll used macros to "recreate" the tab each time I open or close #2.

    Thanks for your help
    Dale

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mirror a tab (Any version)

    You did not want to use the "simple linkage" to get the effect to "whatever's over there, display it here".

    This was to use the multiple links. The major problem is how many links you want: putting a formula in all cells took a lot of memory, but if the workbook could be that large you would have had to use all these formulas.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •