VBA code to click on cell and paste contents in different sheet

[hwkeyser]

Hi all,

I'm new to VBA but I can hold my own explaining what i'm looking for. I'm going to refer to the picture below to help explain.

I am trying to be able to click on a cell in the "Area" Column of the sheet "Zone 1" and have the contents of that clicked cell (ie. 101, 205, etc.) immediately redirect me to worksheet "S11" and paste the value of the clicked cell into the white box beside the word 'area'.

in short- in one click for specific column cells:

Copy clicked cell
Select sheet S11
Select cell (C2)
Paste Special - paste as values.


I have tried this technique thus far:

On the Zone1 worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$5" Then
ActiveCell.Copy
Sheets("S11-Area Lookup").Select
End If
End Sub

So when the re-direct enters the 'S11' sheet

Private Sub Worksheet_Activate()
Range("C2").Select
ActiveSheet.PasteSpecial xlPasteValues
End Sub


This is not what I really want however because then if i go back to check my data on the tab it tries to paste without anything selected and gives me a run-time error: method of pastespecial of object 'worksheet' failed.

I had also made the the 'S11' code specific to "If ActiveRange = "C2" Then" which then causes the same empty clipboard errors as the other procedure.


So in conclusion,
I think the vba code needs to be workbook specific and in one coding: for column A, rows 4-200, on Sheets labeled Zone1[zone2...zone7] to copy the cell, select worksheet 's11', select 'S11'!C2, .pastespecial xlpastevalues.

I could really use help with this as i have the most excel skills of anyone in my office and this is new to me.

I am using Excel 2007.

Thanks,

Henry

 

[Jonmo1]

Try this,

This goes in the Sheet "Zone 1"'s Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A4:A400")) Is Nothing Then
    Sheets("S11-Area Lookup").Select
    Range("C2").Value = Target.Value
End If
End Sub

Hope that helps.

 

[Rick Rothstein]

You are not going to be able to effectively do what you want using a single click; however, you can do it with a double-click. Put this event code in the "Zone 1" sheet module...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    Cancel = True
    If Target.Row > 1 And Len(Target.Value) Then Worksheets("S11").Range("C2").Value = Target.Value
  End If
End Sub

Now, when you double click any cell in Column A from Row 2 downward, and if that cell contains data, then that data will be copied to cell C2 on the S11 sheet.

 

[hwkeyser]

Jonmo: yours would jump to the sheet- but wouldn't seem to paste anything.

Rick: yours does almost exactly what i want- but is there a way that when i double click, it not only copies that data into the cell, but redirects me to the sheet also?


PS, that took you both what? 5 minutes? You guys are awesome.

 

[Jonmo1]

Jonmo: yours would jump to the sheet- but wouldn't seem to paste anything.

You're right, it doesn't paste anything.
It also doesn't copy anything..

What it DOES do,

This line
Range("C2").Value = Target.Value

Basically says:
Let C2 Equal the Value of the Cell that was selected (Target).

Much more efficient than copy / pasting.

 

[Jonmo1]

I see what I did wrong...

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A4:A400")) Is Nothing Then
    With Sheets("S11-Area Lookup")
        .Select
        .Range("C2").Value = Target.Value
    End With
End If
End Sub

 

[Rick Rothstein]

Rick: yours does almost exactly what i want- but is there a way that when i double click, it not only copies that data into the cell, but redirects me to the sheet also?

Sure, no problem....

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    Cancel = True
    If Target.Row > 1 And Len(Target.Value) Then Worksheets("S11").Range("C2").Value = Target.Value
    Worksheets("S11").Activate
  End If
End Sub

 

[hwkeyser]

Guys, both of those worked perfectly. I've been able to figure out what each of those codes do and understand how to modify them for future application.

Thanks so much.

Henry

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[Utradeshow]

I see what I did wrong...

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A4:A400")) Is Nothing Then
    With Sheets("S11-Area Lookup")
        .Select
        .Range("C2").Value = Target.Value
    End With
End If
End Sub

I searched this, and this will also work perfect for my application. However I would like the cell that is Double clicked to "Highlite" Yellow? Is this possible to add?