Copy paste row if value in column = x

[knighttrader]

Hi

Sheet name = 'Test'.
Column AK
Rows 4 to 43 are blank by default.

When value "x" appears in any row 4 to 43 of column AK I want the code to copy and paste special the row the 'x' appears in.
e.g. If 'x' appears in AK15 I want the code to copy and paste special ROW 15

Note: The 'x' value will appear in one row at a time but could appear in any order and time frame; The Sheet name 'Test' may not be open at the time 'x' is added.

Help gracefully received. Thanks

 

[Zot]

Note: The 'x' value will appear in one row at a time but could appear in any order and time frame; The Sheet name 'Test' may not be open at the time 'x' is added.

If the sheet Test is not opened, then where are you entering x? A bit confused here.

 

[knighttrader]

Note: The 'x' value will appear in one row at a time but could appear in any order and time frame; The Sheet name 'Test' may not be open at the time 'x' is added.

If the sheet Test is not opened, then where are you entering x? A bit confused here.

Hi.

I mentioned the sheet is not open because the code will need to reference the sheet ‘Test’ name.

The cells in sheet ‘Test’ AK4:AK43 reference cells in other worksheets using a simple IF formula.

The worksheet is automatically refreshed every second and when the IF condition is met the ‘x’ appears.

Hope that makes sense.

 

[Zot]

There is not need to refresh at certain time interval. You can use worksheet event that anytime there is change in range AK3 to AK43, it will trigger macro to run

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngX As Range
Dim wb As Workbook
Dim wsTest As Worksheet

Set wb = ThisWorkbook
Set wsTest = wb.Sheets("Test")

Set rngX = wsTest.Range("AK4", "AK43")

If Not Intersect(Target, rngX) Is Nothing Then
    If Target = "x" Then
    
'   [I] (your copy and paste special here)[/I]
    
    End If

End If

End Sub

 

[knighttrader]

There is not need to refresh at certain time interval. You can use worksheet event that anytime there is change in range AK3 to AK43, it will trigger macro to run

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngX As Range
Dim wb As Workbook
Dim wsTest As Worksheet

Set wb = ThisWorkbook
Set wsTest = wb.Sheets("Test")

Set rngX = wsTest.Range("AK4", "AK43")

If Not Intersect(Target, rngX) Is Nothing Then
    If Target = "x" Then
   
'   [I] (your copy and paste special here)[/I]
   
    End If

End If

End Sub

Many thanks Zot

It only works when I manually enter x in one of the cells in AK4:AK43.

It doesn't work when the cell is populated by the formula.

Any ideas?

 

[Zot]

Many thanks Zot

It only works when I manually enter x in one of the cells in AK4:AK43.

It doesn't work when the cell is populated by the formula.

Any ideas?

My mistake. I thought you wanted to copy paste when X is typed on those range

So you want to type equation and when the result of calculation is certain value you want to perform copy paste action? Any specific value or several value?

 

[knighttrader]

My mistake. I thought you wanted to copy paste when X is typed on those range

So you want to type equation and when the result of calculation is certain value you want to perform copy paste action? Any specific value or several value?

Each of the cells in AK4:AK43 are looking at cell AH18 in another sheet. The formula is in the screenshot below.

e.g. AK6 looks at Sheet3 AH18. (The cells in Sheet*AH18 are either blank or x based on a formula in those cells. It's not a manual entry.)

When any cell in Sheet TEST AK4:AK43 changes to x (only x) as a result of the formula I want the copy/paste to action.

 

[Zot]

I have these assumption:
1) The value x once shown in range AK4:AK43, it will stay.
2) The value x in specific cell will change from time to time as they area variables and can be any value.

I'm still using Worksheet Change event trigger. The code will scan the range to check for any changes. If the cell change to x, it will trigger action (I popped up msg in this code).

In order to prevent action triggered when the cell was also previous x, I have to remember previous state. I add worksheet Status to remember previous state. You can hide this worksheet from view.

Maybe someone has better approach.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range, rngX As Range
Dim wb As Workbook
Dim wsTest As Worksheet, wsStatus As Worksheet

Set wb = ThisWorkbook
Set wsTest = wb.Sheets("Test")
Set wsStatus = wb.Sheets("Status")

Set rngX = wsTest.Range("AK4", "AK43")

Application.EnableEvents = False
For Each cell In rngX
    If Not cell = wsStatus.Range("A" & cell.Row) Then
        wsStatus.Range("A" & cell.Row) = cell
        If cell = 5 Then
            MsgBox "Text changed to: " & cell.Value & " in " & cell.Address(0, 0)
            Exit For
        End If
    End If
Next
Application.EnableEvents = True

End Sub

 

[Zot]

Forgot to tell you that the x = 5 in my sample.

 

[knighttrader]

Forgot to tell you that the x = 5 in my sample.

Many thanks again Zot. I got it working but only when changing from '_Change' to '_Calculate'. So the final working code is:

Private Sub Worksheet_Calculate()

Dim cell As Range, rngX As Range
Dim wb As Workbook
Dim wsTest As Worksheet, wsStatus As Worksheet

Set wb = ThisWorkbook
Set wsTest = wb.Sheets("TEST")
Set wsStatus = wb.Sheets("Status")

Set rngX = wsTest.Range("AS3", "AS43")

Application.EnableEvents = False
        
        For Each cell In rngX
            If Not cell = wsStatus.Range("A" & cell.Row) Then
            wsStatus.Range("A" & cell.Row) = cell
                 If cell = "X" Then
            
                cell.EntireRow.Value = cell.EntireRow.Value
                 ' MsgBox "Text changed to: " & cell.Value & " in " & cell.Address(0, 0)
  
                Exit For
            End If
        End If
    Next

Application.EnableEvents = True

End Sub

One last thing. I want to save the workbook every time a row is copied/pasted (cell.EntireRow.Value = cell.EntireRow.Value). How do I do that within the code to stop it from getting stuck in a loop saving the file?