Select Previous Cell

[Glasgowsmile]

Hello,

Have the following code which works except it highlights the entire area it posted the data after doing the macro instead of just staying where the cell originally was.

How can I get it to stay on cell B2 after running this code?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("B2:B20")) Is Nothing Then
      Sheets(Target.Value).Range("A1:U50").Copy Range("L1").Value


End Sub

 

[Joe4]

First off, you seem to be missing some code there, like the End If.

Try something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim startCell as Range

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("B2:B20")) Is Nothing Then
      Application.EnableEvents = False
      Set startCell = Target
      Sheets(Target.Value).Range("A1:U50").Copy Range("L1").Value
      startCell.Select
      Application.EnableEvents = True
   End If

End Sub

Note that we want to temporarily disable events while the action part of the code is running, so this procedure doesn't trigger itself.

 

[Glasgowsmile]

First off, you seem to be missing some code there, like the End If.

Try something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim startCell as Range

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("B2:B20")) Is Nothing Then
      Application.EnableEvents = False
      Set startCell = Target
      Sheets(Target.Value).Range("A1:U50").Copy Range("L1").Value
      startCell.Select
      Application.EnableEvents = True
   End If

End Sub

Note that we want to temporarily disable events while the action part of the code is running, so this procedure doesn't trigger itself.

Macro doesn't seem to work at all anymore - I click on the cell that will trigger the macro and nothing happens.

 

[Joe4]

Did you stop the macro part of the way through? It sounds that events got temporarily disabled and never turned back on.

Try running this macro manually, and test the other one again:

Sub FixIt()
    Application.EnableEvents = True
End Sub

 

[Glasgowsmile]

I didn't interrupt anything. I replaced my code with what you provided -- clicked the cell and it did nothing. With my code it works immediately, it just highlights the area instead of sitting in the active cell.

 

[Glasgowsmile]

I've run into another issue using this code -- it works but it's still highlighting the pasted area which I don't want and the other issue now is that it's keeping all the formatting.

If I click on C2, it pulls data from the C2 Sheet but it's formatted just for that sheet so when I click on C3 it needs to be different but because I'm pulling values (due to formulas) it is pasting the data in the previous formatting which makes it look incorrect.

So I need to keep the formatting for each sheet it pulls but I also need to either pull values or find a way for it to pull the actual numbered data instead of the formula itself.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("C2:C20")) Is Nothing Then
      Sheets(Target.Value).Range("A1:U30").Copy
      Range("M1").PasteSpecial xlPasteValues
   End If
End Sub

 

[Joe4]

I didn't interrupt anything. I replaced my code with what you provided -- clicked the cell and it did nothing. With my code it works immediately, it just highlights the area instead of sitting in the active cell.

Then try removing the two "Application.EnableEvents..." commands altogether and see if that make any difference.

 

[Glasgowsmile]

Then try removing the two "Application.EnableEvents..." commands altogether and see if that make any difference.

Thanks - removing that helped, I have a few cells that aren't set up yet to run this macro so it errors out if they get selected and if the enableEvents it wouldn't allow the macro to run any further once that happened.

 

[Joe4]

I have a few cells that aren't set up yet to run this macro so it errors out if they get selected and if the enableEvents it wouldn't allow the macro to run any further once that happened.

Can you provide us with a concrete example (with details), i.e. what cell exactly, what is happening, what is supposed to happen?

 

[Glasgowsmile]

No need - it's corrected. Your code works with removal of the EnableEvents.

Thanks!