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

 

[Glasgowsmile]

I may be misunderstanding your comments on that. I thought with that formula approach, you were saying to use that in lieu of VBA (that you were just using VBA because you didn't think the formula would adjust itself, which it will - but only on the worksheet formula, not in VBA code).

Regarding the Paste Special code, did you see footoo's comments?
Did you give that a try?

BTW, I have been in-and-out of training the paste two days, which is why it sometimes takes a while for me to reply.

I don't know how to break that pastespecial into two lines exactly, so no I didn't.

So I found a work around -- the VBA formula is pulling that Average formula into the cells but it's showing up blank even though it's not blank on the actual spreadsheet and it's referencing the correct sheet/cells, so I don't understand why it's blank.

I moved the average formula to another section of that sheet itself and then changed it to =BYHAR!Q39 instead, so instead of pulling the formula to make the average the VBA code is pulling that formula which works. If you could explain why the previous method didn't work that would be great though.

 

[Joe4]

I don't know how to break that pastespecial into two lines exactly, so no I didn't.

Breaking this line:

      Sheets(Target.Value).Range("A1:U50").Copy Range("M1").PasteSpecial Paste:=xlPasteValues

up into two lines would look something like this:

      Sheets(Target.Value).Range("A1:U50").Copy 
      Range("M1").PasteSpecial Paste:=xlPasteValues
      Application.CutCopyMode = False

the VBA formula is pulling that Average formula into the cells but it's showing up blank even though it's not blank on the actual spreadsheet and it's referencing the correct sheet/cells, so I don't understand why it's blank.

I am sorry, but I really am not following what you are saying here.

 

[footoo]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Or Target(1) = "" Then Exit Sub
If Not Intersect(Target, Range("C2:C18")) Is Nothing Then
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        Sheets(Target.Value).Range("A1:U50").Copy
        Range("M1").PasteSpecial Paste:=xlPasteValues
        Range("M1").PasteSpecial Paste:=xlPasteFormats
        Target.Select
        .CutCopyMode = False
        .EnableEvents = True
    End With
End If
End Sub