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.

Today:	2018-03-09
Daily Road Tax Reserve (-)	-225500
Road Tax Reserve Release (+)	5000000
Excess Availability (-/+)	-1600000
Account -1
Account -2
Account -3
Account -4

<tbody>
</tbody>

	2018-03-05	2018-03-06	2018-03-07	2018-03-08	2018-03-09	2018-03-10	2018-03-11	2018-03-12	2018-03-13
Daily Road Tax Reserve (-)
Road Tax Reserve Release (+)
Excess Availability (-/+)
Account -1
Account -2
Account -3
Account -4
Total Collections

<tbody>
</tbody>

 

[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.