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:

[TABLE="width: 299"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 4"] Whs[/TD]
[/TR]
[TR]
[TD]Lot[/TD]
[TD]Allocated[/TD]
[TD]In[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]XXY 0101[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD]-20 [/TD]
[/TR]
[TR]
[TD]XXY 0102[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0103[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0104[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD]-20 [/TD]
[/TR]
[TR]
[TD]XXY 0105[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD]-20 [/TD]
[/TR]
[TR]
[TD]XXY 0106[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0107[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0108[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0109[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0110[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0111[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0112[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0113[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0114[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]XXY 0115[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]XXY 0116[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0117[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0118[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XXY 0119[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]XXY 0120[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]XXY 0121[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.