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]

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?

 

[Zot]

Yes, using calculate event should be working and better choice in this case I think.
Let me clarify again. Last time you have the formula in Column AK and now changed to column AS, right?

I cannot understand why you are having this line:
cell.EntireRow.Value = cell.EntireRow.Value

This will overwrite the formula that you have, unless you want it to be so.

To save the workbook you can just add line

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
    [B]wb.Save[/B]
Application.EnableEvents = True

Once the condition is met, the line Exit For will jump program out of the loop and execute wb.Save. Since you have cell.EntireRow.Value = cell.EntireRow.Value, the formula in that line is overwritten to just value as I tested it.

 

[knighttrader]

Yes, using calculate event should be working and better choice in this case I think.
Let me clarify again. Last time you have the formula in Column AK and now changed to column AS, right?

I cannot understand why you are having this line:
cell.EntireRow.Value = cell.EntireRow.Value

This will overwrite the formula that you have, unless you want it to be so.

To save the workbook you can just add line

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
    [B]wb.Save[/B]
Application.EnableEvents = True

Once the condition is met, the line Exit For will jump program out of the loop and execute wb.Save. Since you have cell.EntireRow.Value = cell.EntireRow.Value, the formula in that line is overwritten to just value as I tested it.

Yes, the workbook is evolving and col AK code has been moved to col AS.
Yes, I want the formula to be overwritten. Its to create a permanent record.

I tried saving the workbook at that point, but the workbook is refreshed every second and every time its refreshed the workbook is saved. I don't want that to happen. I just want the workbook saved once x is entered in col AS and the row relative row has been copied and pasted.

Any ideas?

 

[knighttrader]

Yes, the workbook is evolving and col AK code has been moved to col AS.
Yes, I want the formula to be overwritten. Its to create a permanent record.

I tried saving the workbook at that point, but the workbook is refreshed every second and every time its refreshed the workbook is saved. I don't want that to happen. I just want the workbook saved once x is entered in col AS and the row relative row has been copied and pasted.

Any ideas?

Ignore my last question. Save works when I put it before Exit For.

Many thanks for all your help.

 

[Zot]

Ignore my last question. Save works when I put it before Exit For.

Many thanks for all your help.

Yes, I was thinking about putting it before Exit For too but I think it would not be any different since save before or after since the saving is once after exiting loop.

Maybe you should add Application.ScreenUpdating = False together with Application.EnableEvents = False to prevent refresh. The program will be smoother and faster. At the end put Application.ScreenUpdating = True to reset. I think even if you don't put it, it will reset to True upon ending the routine, but it is a good practice to put