Vba code to copy & paste values from one sheet to another if condition is met

[Knight_777]

Hi,
Below is a simplified example of tables I have. the 1st tab is in one sheet and the 2nd one is in a different sheet.
Basically what I wont to do is to write a VBA code to copy & paste the value shown in tab 1 to tab 2 in the 2nd sheet if one codition is met, which is the date. In other words, I wont to get the values from tab 1 to be copied in the 2nd tab (which has multiple columns) only if the date in the header of the column match the date as shown in the first cell "2018-03-09" of tab 1. But also, I don't want anything to be copied in the other columns if the criteria is not met, as there will be formulas in those columns which I want preserve if the date is not matching.

I know it sounds complicated, but thought it would be possible to do it through a code.

I would appreciate if anyone could help me on this one, as I'm new to VBA.


Thank you in advance.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: right"]Today:[/TD]
[TD="align: center"]2018-03-09[/TD]
[/TR]
[TR]
[TD]Daily Road Tax Reserve (-)[/TD]
[TD]-225500[/TD]
[/TR]
[TR]
[TD]Road Tax Reserve Release (+)[/TD]
[TD]5000000[/TD]
[/TR]
[TR]
[TD]Excess Availability (-/+)[/TD]
[TD]-1600000[/TD]
[/TR]
[TR]
[TD]Account -1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account -2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account -3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account -4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2018-03-05[/TD]
[TD]2018-03-06[/TD]
[TD]2018-03-07[/TD]
[TD]2018-03-08[/TD]
[TD]2018-03-09[/TD]
[TD]2018-03-10[/TD]
[TD]2018-03-11[/TD]
[TD]2018-03-12[/TD]
[TD]2018-03-13[/TD]
[/TR]
[TR]
[TD]Daily Road Tax Reserve (-)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Road Tax Reserve Release (+)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Excess Availability (-/+)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account -1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account -2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account -3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account -4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Total Collections[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim strdate As String
    strdate = Sheets("Daily Input Form").Range("ReportDate")
    Dim foundDate As Range
    Set foundDate = Sheets("Daily Cash Flow").Rows(6).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
    If Not foundDate Is Nothing Then
        Sheets("Daily Input Form").Range("D7:D11").Copy Sheets("Daily Cash Flow").Cells(24, foundDate.Column)
        Sheets("Daily Input Form").Range("D15:D25").Copy Sheets("Daily Cash Flow").Cells(31, foundDate.Column)
        Sheets("Daily Input Form").Range("D28:D33").Copy Sheets("Daily Cash Flow").Cells(44, foundDate.Column)
    End If
    Application.ScreenUpdating = True
End Sub

 

[Knight_777]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim strdate As String
    strdate = Sheets("Daily Input Form").Range("ReportDate")
    Dim foundDate As Range
    Set foundDate = Sheets("Daily Cash Flow").Rows(6).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
    If Not foundDate Is Nothing Then
        Sheets("Daily Input Form").Range("D7:D11").Copy Sheets("Daily Cash Flow").Cells(24, foundDate.Column)
        Sheets("Daily Input Form").Range("D15:D25").Copy Sheets("Daily Cash Flow").Cells(31, foundDate.Column)
        Sheets("Daily Input Form").Range("D28:D33").Copy Sheets("Daily Cash Flow").Cells(44, foundDate.Column)
    End If
    Application.ScreenUpdating = True
End Sub

Thanks a lot it worked, I just added the statement to copy and paste as value.
Appreciate your help.

 

[mumps]

You are very welcome.