Option Explicit
Private Sub Workbooks_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("J2:K20")) Is Nothing Then 'change range as suited
'
'here do what you need
'or Call a macro
'
End If
End Sub
hmmm, i can't compile and understand how add code in this optionIf cells are changed by keyborad and not by formula or macro you can exploit the event Workbooks_Change. The following example must be placed in the affected sheet's module:VBA Code:Option Explicit Private Sub Workbooks_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("J2:K20")) Is Nothing Then 'change range as suited ' 'here do what you need 'or Call a macro ' End If End Sub
Sub MyCopyRange()
Dim nr As Long
Dim i As Long
Application.ScreenUpdating = False
' Set initial value of next row
nr = 34
' Copy range
For i = 2 To 30840 Step 1
' Copy Range based Cell B66 & B98
If Sheets("sheet1").Range("B" & (nr - 2) * i + 2).Value <> "" Then
Sheets("sheet2").Range("A" & nr * (i - 1) + 7 & ":H" & nr * i + 6).Copy Sheets("sheet2").Range("A" & nr * i + 7)
' Add 34 to next row
Else
Exit Sub
End If
Next i
Application.ScreenUpdating = True
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Union(Range("B66"), Range("B98:B130"))) Is Nothing Then
Call MyCopyRange
End If
End Sub
Sorry, i mean, the specific cells thats a sequence (66,98,130,162,... start from 66 and difference between consecutive numbers is 32) this my range i want(thats exist in the code but im not write code and i don't know which line for make this sequence)Sorry, got the wrong event, use this instead:This macro is not to be add to your macro, it must be copied as it is in Sheet1 vba module.VBA Code:Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Union(Range("B66"), Range("B98:B130"))) Is Nothing Then Call MyCopyRange End If End Sub
When you change data in range B66 or B98:B130 your macro MyCopyRange will be then executed.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 2 And Target.Row >= 66 Then
If (Target.Row - 34) Mod 32 = 0 Then
'
' do your stuff
'
End If
End If
End Sub
Thank You, This is what I wantedPerhaps this is what you are after, it goes in the worksheet module ...
VBA Code:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 And Target.Column = 2 And Target.Row >= 66 Then If (Target.Row - 34) Mod 32 = 0 Then ' ' do your stuff ' End If End If End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
If Target.Count = 1 And Target.Column = 2 And Target.Row >= 66 Then
If (Target.Row - 34) Mod 32 = 0 Then
If Target.Value <> "" Then
lRow = (((Target.Row - 34) * 34) / 32) + 7
With ThisWorkbook.Sheets("Sheet2")
.Range("A" & lRow & ":H" & lRow + 33).Copy .Range("A" & lRow + 34)
End With
End If
End If
End If
End Sub
How to change number 66 in first lf line to 67? and next target cell, 99,131,... (that you say every 32 cell after that)You are welcome and thanks for letting me know.
Meanwhile a took a closer look at your code and I think you'll ultimately were looking for something like the code below.
The code runs on every change of your worksheet. It checks if the change took place in column B. If that's the case it checks wether the cell is on row 66, 98, 130, 162 (or ...+32 and so on). If that's the case it checks wether the change results in any value or any text. If so than the code performs a copy action equivalent to that of your original code.
VBA Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim lRow As Long If Target.Count = 1 And Target.Column = 2 And Target.Row >= 66 Then If (Target.Row - 34) Mod 32 = 0 Then If Target.Value <> "" Then lRow = (((Target.Row - 34) * 34) / 32) + 7 With ThisWorkbook.Sheets("Sheet2") .Range("A" & lRow & ":H" & lRow + 33).Copy .Range("A" & lRow + 34) End With End If End If End If End Sub