View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
flummi
 
Posts: n/a
Default Linked cells with validation

I've made the code a bit more readable.

sr = 3 ' start row of data on sheet1
er = 20 ' end row of data on sheet1
ss = 2 ' first sheet# to start copying to (start row = 1)
es = 3 ' last sheet# to copy to (start row = 1)

sr = 1 ' start row on wheet1
er = 20 ' end row on sheet1
ss = 2 ' first sheet# to start copying to
es = 3 ' last sheet# to copy to
For s = ss To es
sn = "sheet" + CStr(s)
For k = sr To er
Worksheets(sn).Cells(k, 1).Value = ""
Worksheets(sn).Cells(k, 2).Value = ""
Next k
Next s
For s = ss To es
sn = "sheet" + CStr(s)
k = 1
For i = 1 To 20
If Worksheets("Sheet1").Cells(i, 1).Value < "" Then

'the code on the following 3 lines is broken into 6 lines in the post.
You will have to re-join them.

If Worksheets("Sheet1").Cells(i, 1).Value =
Worksheets(sn).Cells(1, 5).Value Then
Worksheets(sn).Cells(k, 1).Value =
Worksheets("Sheet1").Cells(i, 1).Value
Worksheets(sn).Cells(k, 2).Value =
Worksheets("Sheet1").Cells(i, 2).Value
k = k + 1
End If
End If
Next i
Next s

That code works fine here.

Assumptions a

You define a selection criterion in cell E1 on all sheets where you
want the selected data to go
In the example the data is in A1:B20,
The filtered data on each sheet is in A1:B20

If you put the code into the "sheetactivate" event of your workbook
then the sheets will be updated when you select it.
You can also define a command button on every page and insert the code
into each button. That will alow you to update the sheets by clicking
the button when you e.g. modified the selection criteron.

If it still doesn't do it for you let me know.

Hans