Copy values of previus cell, if the value is not zero

[ppvivanco]

Hi,

I need to do the following macro:

I have one shipment report, where I put the quantity (in Metric tons) that I sent to each place. One place is our warehouse, and I need, if the lot appear like delivered, one macro to show the number of lots delivered in other range.

The table is the following:

Whs
Lot	Allocated	In	Balance
XXY 0101	20		-20
XXY 0102
XXY 0103
XXY 0104	20		-20
XXY 0105	20		-20
XXY 0106
XXY 0107
XXY 0108
XXY 0109
XXY 0110
XXY 0111
XXY 0112
XXY 0113
XXY 0114	20	20	D
XXY 0115	20	20	D
XXY 0116
XXY 0117
XXY 0118
XXY 0119	20	20	D
XXY 0120	20	20	D
XXY 0121

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

And I need, if the Balance show "D", the values (XXY 0120 in example) copied in other place. If this possible?

Thanks

 

[NeonRedSharpie]

Sub moveThings()

    For x = 2 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        If Cells(x, 4).Value = "D" Then
            Cells(x, 1).Copy Destination:= 'somewhere
        End If
    Next x
End Sub

 

[ppvivanco]

Sub moveThings()

    For x = 2 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        If Cells(x, 4).Value = "D" Then
            Cells(x, 1).Copy Destination:= 'somewhere
        End If
    Next x
End Sub

Hi, Thanks for your answer.

I put the results in A30 (Cells(x, 1).Copy Destination:=Range("A30")), but only show one result, and not all. Any idea?


Thanks in advance,

 

[NeonRedSharpie]

Sub moveThings()

    Dim nextRow As Long


    nextRow = 56


    For x = 2 To ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
        If Cells(x, 4).Value = "D" Then
            Cells(x, 1).Copy Destination:=Cells(nextRow, 1)
            nextRow = nextRow + 1
        End If
    Next x
End Sub

I moved the criteria to count cells to column D because adding below A would increase with each run time. Very small change.

 

[ppvivanco]

Sub moveThings()

    Dim nextRow As Long


    nextRow = 56


    For x = 2 To ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
        If Cells(x, 4).Value = "D" Then
            Cells(x, 1).Copy Destination:=Cells(nextRow, 1)
            nextRow = nextRow + 1
        End If
    Next x
End Sub

I moved the criteria to count cells to column D because adding below A would increase with each run time. Very small change.

Yes, it is working correctly.

Thanks!!

 

[ppvivanco]

Last thing,

How I can to do for paste the values in the first empty cell from A56? For example, I configure the Macro to paste from A56, but manually I wrote in A56, A57, A58, etc. How I can configure the Macro for paste in first blank cell?

Regards,

 

[NeonRedSharpie]

The first blank cell AFTER A56 or after the data set?

 

[ppvivanco]

The first blank cell AFTER A56 or after the data set?

The better way is after the data set if the lot (Column A) doesn't appear yet. But if not possible, only first blank after the A56.