VBA Copy & Paste Values onto itself based on Criteria

[mst3kr]

Hello.

I have a file of roughly 250 lines that I update 3x/day where the primary report page is filled with formulas as the background data will change throughout the day, day to day. There is a Status column that is manually updated each time the report is refreshed and an order shows it is Ready. When the status is Ready, I need a cell range in that row to have its data copied & pasted as a value onto itself. I also need this copy & paste exercise to be duplicated for all rows where the Status is Ready. I have several VBAs currently written in the file but haven't been able to write one that will read all lines each time a script is run...

Below is a sample of what the spreadsheet looks like. Any help would be appreciated!

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
PO	Ordered	Due	Item #	Order Qty	Units	Pallets	Item	Job #	Mach	Ready Date	Ready Time	Status	Carrier	Trailer
1	4-Jan	07-Feb	1	5	2	20.0	2	1		7-Feb	18:00
2	11-Jan	07-Feb	2	10	4	20.0	4	3		1-Feb	12:43	Ready		1
3	26-Jan	07-Feb	3	15	6	20.0	6	5		1-Feb	18:11	Shipped		2
4	26-Jan	07-Feb	4	20	8	20.0	8	7		6-Feb	15:30
5	26-Jan	07-Feb	5	25	10	20.0	10	9		2-Feb	00:48	Shipped		3
6	14-Dec	08-Feb	6	30	12	20.0	12	11		2-Feb	13:43	Ready		4
7	11-Jan	08-Feb	7	35	14	20.0	14	13		26-Jan	17:21	Ready
8	26-Jan	08-Feb	8	40	16	20.0	16	15		2-Feb	10:55	Ready		5
9	26-Jan	08-Feb	9	45	18	20.0	18	17		2-Feb	02:33	Ready		6
10	26-Jan	08-Feb	10	50	20	20.0	20	19		1-Feb	21:03	Ready		7

 

[Coyotex3]

Give this code a shot. This will paste the entirerow as values. Can you specify what range you want to copy and paste as value?

Sub mst3kr()
Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lr

    If Range("M" & i).Value = "Ready" Then Range("A" & i).EntireRow.Value = Range("A" & i).EntireRow.Value
Next
End Sub

 

[kevin9999]

Another option...

Sub Convert_Ready()
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        With .Range("A1").CurrentRegion
            .AutoFilter 13, "Ready"
            If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
                With .Offset(1).SpecialCells(xlCellTypeFormulas)
                    .Value = .Value
                End With
            End If
            .AutoFilter
        End With
    End With
End Sub

 

[dermie_72]

I had a look at an event macro for you, so you don't need to run it manually each time, but will check the data to see if the cells have changed and if it equals Ready if will run. It checks column M because thats the one you have in your test data, but if the ranges are different you can update the code to match your data. This needs to be placed in the Sheet, not in the module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M:M")) Is Nothing Then
    If Target.Value = "Ready" Then
        Range("A" & Target.Row & ":L" & Target.Row).Value = Range("A" & Target.Row & ":L" & Target.Row).Value
        Range("N" & Target.Row & ":O" & Target.Row).Value = Range("N" & Target.Row & ":O" & Target.Row).Value
        Else
    End If
End If
End Sub

 

[mst3kr]

Coyote, kevin, dermie... Thank you very much for your help! I was able to test each of your codes and (after making range adjustments) they all work perfectly! I really appreciate your assistance with this! This will make things MUCH easier when dealing with this file everyday...