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]

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

For some reason, running into an issue with the code now.

I need it to pull the values and formatting -- but not the formulas, can you help with that?

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("C2:C18")) Is Nothing Then
      Set startCell = Target
      Sheets(Target.Value).Range("A1:U50").Copy Range("M1")
      startCell.Select
   End If


End Sub

 

[Joe4]

Sounds like you want Copy Paste Special Values to turn those formulas into values.
See here: https://www.thespreadsheetguru.com/the-code-vault/best-way-to-copy-pastespecial-values-only-with-vba

 

[Glasgowsmile]

Or alternatively -- if you could teach me a formula to add that it could pull without the issues I'm running into.

This is the formula I'm using -- problem is, the reference cells are only in that specific sheet, so when it pulls the formula to the other sheet it's looking at completely different data instead of pulling the reference sheets from the sheet.

I know I could do SheetName!B$40 or something like that but I need it to be dynamic in case the sheet name changes so I don't have to constantly worry about updating it.

=IF(B$40="","",AVERAGE(B$40:B$300))

 

[Glasgowsmile]

Sounds like you want Copy Paste Special Values to turn those formulas into values.
See here: https://www.thespreadsheetguru.com/the-code-vault/best-way-to-copy-pastespecial-values-only-with-vba

I've tried this but I get a syntax error and I need it to pull the formatting too, not just the values.

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("C2:C18")) Is Nothing Then
      Set startCell = Target
      Sheets(Target.Value).Range("A1:U50").Copy Range("M1").PasteSpecial Paste:=xlPasteValues
      startCell.Select
   End If




End Sub

 

[Glasgowsmile]

Paste Value

Good Evening,

Could anyone help me with the code below -- I need to paste value and format as currently it's pasting formulas for some cells that need to keep the reference in the sheet they're currently in instead of being pulled to this sheet.

I tried adding Range("M1").PasteSpecial xlPasteValuesAndFormats but I get a syntax error each time.

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("C2:C18")) Is Nothing Then
Set startCell = Target
Sheets(Target.Value).Range("A1:U50").Copy Range("M1")
startCell.Select
End If


End Sub

 

[Joe4]

Or alternatively -- if you could teach me a formula to add that it could pull without the issues I'm running into.

I know I could do SheetName!B$40 or something like that but I need it to be dynamic in case the sheet name changes so I don't have to constantly worry about updating it.

That actually is dynamic. If you are using that formula, and then change the name of that sheet, the formula will automatically change to the new name of the sheet.
Try it and see!

 

[footoo]

Re: Paste Value

Use 2 lines of code for the pastespecial - one for pasting value and one for format.

 

[Joe4]

Re: Paste Value

I have merged your two threads together. Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be typically locked or deleted (rule 12 here: Forum Rules).

 

[Glasgowsmile]

That actually is dynamic. If you are using that formula, and then change the name of that sheet, the formula will automatically change to the new name of the sheet.
Try it and see!

So I tried adding the sheet name into the formula -- didn't work, the VBA code is pulling the formula but it's showing up blank for some reason.

=IF(BYHAR!Q$40="","", AVERAGE(BYHAR!Q$40:Q$300))

 

[Joe4]

So I tried adding the sheet name into the formula -- didn't work, the VBA code is pulling the formula but it's showing up blank for some reason.

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.