Copy & Paste Problem

[MartinP_uk]

The code below copies and paste O5:O14 into Y5:Y14. However it also pastes into S5:S:14 which i do not require. Any idea how i can stop the pasting in column S please?

Code: Select all

 Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextCol As Long
Application.EnableEvents = False

NextCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1

Range("O5:O14").Copy
Cells(5, NextCol).PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
End Sub

[osknows]

Are you trying to paste into the same Y5:Y14 every time or Y5:Y14, Z5:Z14, AA5:AA14...etc?

[MartinP_uk]

Y5:Y14, Z5:Z14, AA5:AA14...etc? thats correct m8.

[osknows]

There's a few ways to do this, perhaps easiest method is

this in a MODULE

Code: Select all

Public Nextcol As Integer

and this in the SHEET

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Nextcol >= 200 Then
Nextcol = 0
End If

ThisWorkbook.Sheets(Target.Worksheet.Name).Range("y5:y14").Offset(0, Nextcol).Value = _
ThisWorkbook.Sheets(Target.Worksheet.Name).Range("o5:o14").Value
Nextcol = Nextcol + 1
Application.EnableEvents = True
End Sub


Nextcol will increment to 200 before resetting to 0, so that gives you column Y(0) to HQ(200) before resetting back to column Y

[MartinP_uk]

Thank you osknows your knowledge of this kind of things seems to be fast cheers