Issues with "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" - Code restarting from pasting

[Stildawn]

Hi All

Have a complicated sheet that does things on the fly as the user changes fields and enters data etc etc.

To do this I use: Private Sub Worksheet_SelectionChange(ByVal Target As Range) on Sheets(1)

Which as you know runs the code whenever the selection changes on the sheet, which is great.

My issue is that I have this code within the SelectionChange sub:

With Thisworkbook.Sheets(1)
Lastrow = ThisWorkbook.Sheets(2).Range("A" & ThisWorkbook.Sheets(2).Rows.Count).End(xlUp).Row
ThisWorkbook.Sheets(2).Range("C" & Lastrow & ":G" & Lastrow).Copy
.Range("D" & i).PasteSpecial xlPasteValues
End With

There is massively more code within the With block but that is the relevant part.

What happens is when the ".Range("D" & i).PasteSpecial xlPasteValues" line is run, the code takes that as a selectionchange, and then the whole selectionchange code runs again from the start.

Which is screwing up the rest of the code below ".Range("D" & i).PasteSpecial xlPasteValues"

Any ideas on how to get around this?

Regards

 

[fhqwgads]

i'm not sure on the exact syntax but maybe something involving destination?

ThisWorkbook.Sheets(2).Range("C" & Lastrow & ":G" & Lastrow).Copy destination:=.Range("D" & i).PasteSpecial xlPasteValues

not sure if that also changes the selection like paste does to trigger the selection change

EDIT: disregard that, you can't use pastespecial with destination according to https://www.mrexcel.com/forum/excel-questions/666907-vba-pastespecial-using-destination-command.html

 

[fhqwgads]

ok try this

With Thisworkbook.Sheets(1)
Lastrow = ThisWorkbook.Sheets(2).Range("A" & ThisWorkbook.Sheets(2).Rows.Count).End(xlUp).Row
ThisWorkbook.Sheets(2).Range("C" & Lastrow & ":G" & Lastrow).Copy Destination:=.Range("D" & i)
.Range("D" & i, .Range("D" & i).Offset(ThisWorkbook.Sheets(2).Range("C" & Lastrow & ":G" & Lastrow).Rows.Count - 1, ThisWorkbook.Sheets(2).Range("C" & Lastrow & ":G" & Lastrow).Columns.Count - 1)).Clearformats
End With

i was trying to use destination to get your values into the cells you want and then clear the formatting from those cells by getting the shape of your copy area and offsetting it to the destination. i hope that works with the With .Range code

 

[fhqwgads]

wait sorry i was assuming you were pasting values to avoid formats like highlights or font. i didn't consider it was to get values from formulas. destination will paste the formulas
sorry i couldnt help

 

[fhqwgads]

bump for help
i hope someone can solve your problem since i wasnt any use

 

[RoryA]

You can disable events while pasting like this:

With Thisworkbook.Sheets(1)
Lastrow = ThisWorkbook.Sheets(2).Range("A" & ThisWorkbook.Sheets(2).Rows.Count).End(xlUp).Row
ThisWorkbook.Sheets(2).Range("C" & Lastrow & ":G" & Lastrow).Copy
Application.enableevents = false
.Range("D" & i).PasteSpecial xlPasteValues
Application.enableevents = true
End With