Fetching more rows / Dynamic linkages in a workbook

[thunderhead]

Hi all,

A few queries with regard to Excel/VBA/Macros:

a.) I have a bunch of data on a worksheet in a workbook. I am displaying a part of this data on another worksheet in a different workbook.

I want a control by which the user can choose to view more data from the first worksheet.

For example, I have some names and telephone numbers in the first workbook. I display some 5 five of these on another workbook. I want to have a button or spin control, which when clicked can display another 5 entries from the first workbook (or, in case of the spin control, display as many as the user wants uptil the last entry...).

How do I accomplish this?

b.) Also, let's say I have a workbook which is dynamically linked to some other workbooks for data sourcing purposes. I want to distribute this workbook to others, but only with the latest updated data from the other workbooks (in other words, I don't want to send them all the linked workbooks, just the one workbook with the latest updated data from my machine). Is there an easy way to toggle the dynamic linkage on and off, or replicate my workbook with just the values onto another one for the purposes of distribution? (Better still, would it be possible to have a control on my workbook which when called upon can create another worksheet with just the latest values present in my workbook [let's say it's just one worksheet that I want to distribute]?).

c.) How do I add a control to the worksheet, which when used can change the active worksheet to another worksheet on the same workbook and display it?

Thanks a ton for all your insights on this.

 

[hatman]

[deleted duplicate post]

 

[hatman]

Let's work through from EASY to Hard:

b: Edit->Links, then select Break Links from the dialog. This will convert all formulaic references to other workbooks to the most updated Text Value.

c: What about the Sheet Tab buttons at the bottom of the window? Alternatively, you can add a Command Button with this code:

Private Sub CommandButton1_Click()

    ThisWorkbook.Worksheets("Sheet2").Activate
    
End Sub

a: There are a variety of ways to do this. I assume that you want to do this on the sheet directly? I would add a spin control with this code:

Private Sub SpinButton1_SpinDown()
    If Replace(Application.Names("Index").Value, "=", "") > 1 Then
    
        Application.Names("Index").Value = "=" & Replace(Application.Names("Index").Value, "=", "") - 1
    
    End If
End Sub

Private Sub SpinButton1_SpinUp()
If Replace(Application.Names("Index").Value, "=", "") < 100 Then
    
        Application.Names("Index").Value = "=" & Replace(Application.Names("Index").Value, "=", "") + 1
    
    End If
End Sub

And something like this formula to get the values from a closed workbook:

=INDIRECT.ext("'U:\proceng\wip\test_procs\wpoga\oga\Administration\PES Stuff\[PES Document Index.xls]Data'!$I$"&Index)

with appropriate substitutions for your file/path names... and just increment each formula Index+1, Index +2... as required. I recommend using Indirect.ext from MoreFunc (by Laurent Longre) so you can extract values from the workbook while closed.

There are other options for doing this, too, but this one is a little simpler than others...