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]

It wouldn't be something 'added' to the previous code.
It would be a new seperate code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Interior.ColorIndex = 6
End Sub

Hope that helps.

 

[Rick Rothstein]

It wouldn't be something 'added' to the previous code.
It would be a new seperate code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Interior.ColorIndex = 6
End Sub

Jonmo1's code above will do exactly what you asked for, but I am wondering... if you double-click a different cell, do you want that previously double-clicked cell to remain yellow or not?

 

[Utradeshow]

Rick,

I was just noticing that, I would like the previous call to not be yellow. If we could be really fancy, I would like it to highlite the cell next to the target cell as well if possible

 

[Rick Rothstein]

Rick,

I was just noticing that, I would like the previous call to not be yellow. If we could be really fancy, I would like it to highlite the cell next to the target cell as well if possible

Give this modification to Jonmo1's code a try...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cells.Interior.ColorIndex = xlColorIndexNone
  Target.Resize(, 2).Interior.ColorIndex = 6
  Cancel = True
End Sub

NOTE: It was not clear if you wanted to enter Edit Mode when you double clicked the cell or not. I assumed not, but if that is wrong, then remove the Cancel=True code line.

 

[Utradeshow]

That does work almost perfect, However it removes all cell shading and colors on the entire sheet.

 

[Rick Rothstein]

That does work almost perfect, However it removes all cell shading and colors on the entire sheet.

Well, so will double clicking on or next to an already colored cell with the original code (which is why I assumed you did not have any colored cells). Is there a range that you want to restrict this double click functionality? If so, what is it?

 

[Utradeshow]

Yes, the range is V2:W15 with V2:V15 being the target cells to doubleclick

 

[Rick Rothstein]

Yes, the range is V2:W15 with V2:V15 being the target cells to doubleclick

Okay, see if this does what you want...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("V2:V15")) Is Nothing Then
    Range("V2:W15").Interior.ColorIndex = xlColorIndexNone
    Target.Resize(, 2).Interior.ColorIndex = 6
    Cancel = True
  End If
End Sub

 

[Utradeshow]

That is awesome!! Thank you so much!