opying into cells if particular criteria is met

[Mazbuka]

I have an excel journal template that I want completed from a cashbook type source...I'll show a simple example:

Say this is my cashbook



[TABLE="width: 571"]
<colgroup><col span="4"><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD] [/TD]
[TD]C[/TD]
[TD] [/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]DATE[/TD]
[TD] [/TD]
[TD]DETAIL[/TD]
[TD] [/TD]
[TD]AMOUNT[/TD]
[TD] [/TD]
[TD]CASH/CHECK[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]John[/TD]
[TD] [/TD]
[TD]1000[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]Joan[/TD]
[TD] [/TD]
[TD]1200[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]Fred[/TD]
[TD] [/TD]
[TD]1500[/TD]
[TD] [/TD]
[TD]CHECK[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]Pat[/TD]
[TD] [/TD]
[TD]800[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]Mary[/TD]
[TD] [/TD]
[TD]900[/TD]
[TD] [/TD]
[TD]CHECK[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]Steve[/TD]
[TD] [/TD]
[TD]750[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 571"]
<colgroup><col span="4"><col><col span="3"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need relevant cells copied into the cash journal below

If D2 above = "cash", copy A2, B2, C2 & D2 above into A2, C2, D2 & E2 below respectively.

However if D2 above = "check" then Look at D3 above, if that's = "cash" copy from row 3 above into row 2 below, if it's ="check" then disregard and look at D4 & so on.

End result will look like this.


[TABLE="width: 852"]
<colgroup><col span="12"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 848"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD] [/TD]
[TD]C[/TD]
[TD] [/TD]
[TD]C[/TD]
[TD] [/TD]
[TD]D[/TD]
[TD] [/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]DATE[/TD]
[TD] [/TD]
[TD]REFERENCE[/TD]
[TD] [/TD]
[TD]DETAIL[/TD]
[TD] [/TD]
[TD]DOC NUM[/TD]
[TD] [/TD]
[TD]AMOUNT[/TD]
[TD] [/TD]
[TD]CASH/CHECK[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]17-10-19[/TD]
[TD][/TD]
[TD]OTHER FIXED DATA[/TD]
[TD][/TD]
[TD]John[/TD]
[TD] [/TD]
[TD]OTHER FIXED DATA[/TD]
[TD] [/TD]
[TD]1000[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]OTHER FIXED DATA[/TD]
[TD] [/TD]
[TD]Joan[/TD]
[TD] [/TD]
[TD]OTHER FIXED DATA[/TD]
[TD] [/TD]
[TD]1200[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]OTHER FIXED DATA[/TD]
[TD] [/TD]
[TD]Pat[/TD]
[TD] [/TD]
[TD]OTHER FIXED DATA[/TD]
[TD] [/TD]
[TD]800[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD]17-10-19[/TD]
[TD] [/TD]
[TD]OTHER FIXED DATA[/TD]
[TD] [/TD]
[TD]Steve[/TD]
[TD] [/TD]
[TD]OTHER FIXED DATA[/TD]
[TD] [/TD]
[TD]750[/TD]
[TD] [/TD]
[TD]CASH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

You should take a look at AutoFilter ...

Hope this will help

 

[mumps]

Assuming your cashbook and cash journal are in two separate sheets in the same workbook and are named "Cashbook" and "Cash Journal" respectively, try this macro:

Sub copyData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Cashbook")
    Set desWS = ThisWorkbook.Sheets("Cash Journal")
    Dim LastRow As Long, header As Range, fnd As Range
    LastRow = srcWS.Range("A" & srcWS.Rows.Count).End(xlUp).Row
    With srcWS.Cells(1, 1).CurrentRegion
        .AutoFilter 4, "CASH"
        With srcWS
            For Each header In .Range("A1:D1")
                Set fnd = desWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    .Cells(2, header.Column).Resize(LastRow - 1).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, fnd.Column).End(xlUp).Offset(1, 0)
                End If
            Next header
        End With
    End With
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Mazbuka]

Assuming your cashbook and cash journal are in two separate sheets in the same workbook and are named "Cashbook" and "Cash Journal" respectively, try this macro:

Sub copyData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Cashbook")
    Set desWS = ThisWorkbook.Sheets("Cash Journal")
    Dim LastRow As Long, header As Range, fnd As Range
    LastRow = srcWS.Range("A" & srcWS.Rows.Count).End(xlUp).Row
    With srcWS.Cells(1, 1).CurrentRegion
        .AutoFilter 4, "CASH"
        With srcWS
            For Each header In .Range("A1:D1")
                Set fnd = desWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    .Cells(2, header.Column).Resize(LastRow - 1).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, fnd.Column).End(xlUp).Offset(1, 0)
                End If
            Next header
        End With
    End With
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Thanks very much...I'll play around with this get an understanding of it.