CODEWITHSUNDEEP
vbaexcel
nam24
nam24

Reputation: 51

copy/paste values with the if function

I am trying to write a code in VBA that will copy values from another worksheet into a master worksheet whenever the master worksheet is a particular day. My data is set up like this:

In column A of the master worksheet, I have my dates (mm/dd/yy) for the particular month. Columns B and C have data that isn't relevant to the calculation but need to be there. Columns D through G is where I want to paste the values from the source worksheet. For example, if A19 is 02/13/15, I would want the macro to pull data from the source worksheet into cells D19:G19.

Here's the code I am using so far. I would appreciate any help. Thanks!

Sub newline()

strdate = InputBox("Date")
D = CStr(strdate)
's4 is the master worksheet
Set s4 = Sheets(4)
's5 is the source worksheet that updates every day with different data
Set s5 = Sheets(5)

n = s4.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

i = 0

For j = 1 To n
    If s4.Cells(j, 1).Value = D Then
    i = i + 4
    'name of the cells in the source worksheet
    Set r = s5.Range("AccrualPayments")
    r.Copy
    s4.Cells(j, i).PasteSpecial (xlPasteValues)
    End If
Next j

End Sub

Upvotes: 1

Views: 1047

Answers (1)

Kyle
Kyle

Reputation: 2545

Are you running into an error somewhere? Or is there something in particular this is not doing?

EDIT:

Sub newline()
Dim strdate as String
Dim s4, s5 as Worksheet
Dim i, j, n as Integer

strdate = InputBox("Date")

's4 is the master worksheet
Set s4 = Sheets(4)
's5 is the source worksheet that updates every day with different data
Set s5 = Sheets(5)

n = s4.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

i = 0

For j = 1 To n
    If s4.Cells(j, 1) = strdate Then
    i = i + 4
    'name of the cells in the source worksheet
    s5.Range("AccrualPayments").copy
    s4.Cells(j, i).PasteSpecial Paste:=xlPasteValues
    End If
Next j

End Sub

Make sure that the date entered and the date on your master sheet are in the same format. For instance, February 2nd, 2015 could be written "2/2/2015", "02/2/2015" or "02/02/2015". Otherwise, it seems as though this code should work.

Will each date show up only once in your sheet "s4"? If not, the first instance will have the range "AccrualPayments" pasted in column D, the second in column H, the third L, etc. If they only show up once, this will not be an issue.

Upvotes: 1

Related Questions