Selecting sheet and row by cell value

[Skebo]

I'm trying to create paste links between a number of cells on various sheets.

On sheet1 I have two columns that contain validation lists. The values in the columnA (first list) correspond to worksheet names. The values in columnB (second list) corresponds to values found in column D on the other sheets.

I want the user to select a value from each list and also to input data into column C on sheet1.
Based upon the two values I would think you could select the proper sheet and row and then paste the value from column C into that specific row on that specific sheet.

Something like:
Sheets(ActiveCell.Value).Select ???

I don't know where to begin on the row selection.
Any guidance would be greatly appreciated.

 

[Guest]

I'd like to know how to do something very similar to this.

 

[Derek]

Hi there

Try this, it might give you some ideas:
Sheet 1 Column A = validation list showing sheet names ( type names in list like Sheet2 )
Sheet 1 Column B = validation list showing values which correspond to identical values in D:D of other sheets
Sheet 1 Column C is for data to be transferred to appropriate sheet (pastes in cell to right of value selected)
Sheet1 Put this formula in D1 and scroll down
="E"&MATCH(B1,INDIRECT((A1)&"!D:D"),0)
(E is the column your data will paste into)

Right click Sheet1 tab, left click View Code then paste in the following code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Application.ScreenUpdating = False
Range(Target, Target.Offset(0, 1)).Copy
Sheets(Target.Offset(0, -2).Value).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Range("A1").Copy
ActiveSheet.Range(ActiveSheet.Range("B1").Value).Select
ActiveSheet.Paste
ActiveSheet.Range("A1:B1").ClearContents
Sheets("Sheet1").Select
Application.CutCopyMode = False
End If
End Sub

It will activate after a data entry is made in a cell in Sheet1 Column C
Be aware that it first pastes values to cells A1:B1 on the sheet in question. A1 gets your data and B1 gets the address to put it on that sheet. It then copies A1 and pastes it into the address shown in B1. It then clears A1:B1

Hope this helps a bit
Good Luck
Derek

 

[Skebo]

Derek,
You're an All-Star. Thanks.

I have one other issue, but I haven't really done my homework on it yet. I might be able to find an answer elsewhere on the site, but any input you have would be great!

D may contain multiple rows with the same value. In cases where we have already filled in E, I would like it to look for the next matching D with an unfilled E.

 

[Derek]

Hi again Skebo

Here is the simplest solution I could devise to your question but it involves using a hidden column on each of your other sheets. Here's what you do:

1. Insert a column to left of D:D in all other sheets (old D:D is now E:E and data will paste into F:F)
2. Paste this formula in D1 of all other sheets:
=(IF(ISBLANK(F1),E1,""))
3. Scroll formula down column D as far as you will ever have data
4. Hide column D:D in all other sheets
5. In Sheet1 D1 change the formula to:
=IF(ISERROR("F"&MATCH(B1,INDIRECT((A1)&"!D:D"),0)),"NO TARGET !","F"&MATCH(B1,INDIRECT((A1)&"!D:D"),0))
6. Change the event macro code to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Range("D1").Value = "NO TARGET !" Then
GoTo ErrorHandler
Else
Application.ScreenUpdating = False
Range(Target, Target.Offset(0, 1)).Copy
Sheets(Target.Offset(0, -2).Value).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Range("A1").Copy
ActiveSheet.Range(ActiveSheet.Range("B1").Value).Select
ActiveSheet.Paste
ActiveSheet.Range("A1:B1").ClearContents
Sheets("Sheet1").Select
Application.CutCopyMode = False
End If
End If
Exit Sub
ErrorHandler:
CutCopyMode = False
MsgBox "NO TARGET"
Exit Sub
End Sub

The formula in Sheet1 D1 still searches for a target in D:D (which is hidden). D:D replicates E:E provided F:F is blank (so only targets with nothing against them in F are shown in D:D forcing the formula to the next suitable match.

D1 in Sheet1 will display a cell reference if a free target is available. When it runs out of targets it will display "NO TARGET !". If you try to make an entry a message box warning appears and the macro exits.

This was apparantly a challenge for both me and my computer which blew its surge protector midway through an attempt at a reply!

Hope it works for you
Have fun
Derek
This message was edited by Derek on 2002-03-02 03:52

 

[Skebo]

Derek,
Sorry to hear about your hardware problems, but thanks for the reply.
I made one small change to IF THEN statement for the error handler. I changed:
If Range("D1").Value = "NO TARGET !" Then

to

If ActiveCell.Offset(0, 1).Value = "NO TARGET !" Then

This works great, although my orginal intent was to use paste-links, which I think would allow for changes on sheet1. With the current situation where you write the data to A1:B1, this won't work. Is there some specific reason that has to be done?
This message was edited by Skebo on 2002-03-04 12:40

 

[Derek]

Hi again
Sorry mate but we're moving into territory somewhat ahead of my abilites now Skebo.

Look I am sure it is possible in vb to paste directly to the correct cell on the other sheet/s but my vb skills are ultra basic. I wasn't able to fully master the syntax for referencing ranges between the sheets in these circumstances. So for my skill level it was easier first to paste the data and its destination address into A1:B1 of the other sheet, then to reference it from there.

I cannot quite visualise the paste link scenario - because as it stands if you change the original data in Sheet1 Column C then the macro will immediately paste that data to the next available target in the other sheet. So I guess you should change the event that triggers the macro to doubleclick (ie when you doubleclick the data in Sheet1 Col C, it pastes to the other sheet). This will allow you to change the data to update the link without triggering the macro again.

I suggest you start a new post to get the attention of one of the vb gurus - tell them what you've got so far and see if they can improve my code to achieve what you require.
Good luck
Derek

 

[Derek]

Skebo
Ah, I think this is what you are after. Change the event macro to this and doubleclick your data entry to activate it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 3 Then
Application.ScreenUpdating = False
Sheets(Target.Offset(0, -2).Value).Range(Target.Offset(0, 1).Value).Formula = "=" & "Sheet1!" & Target.Address
End If
End Sub

good luck
Derek
This message was edited by Derek on 2002-03-05 22:49