Selecting sheet and row by cell value
Selecting sheet and row by cell value
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Selecting sheet and row by cell value

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    Guest

    Default

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com