Macro to index/look up cell and place data onto another sheet

[ardykav]

Hi All,

I have two sheets, Weekly Sales and Yearly Sales. Column A on Weekly Sales has the 7 days of the week running from cells A10:A16, On the yearly sales the dates run from cell A3:A368.

The idea is that the weekly sheet is populated and then a macro copies all data from the weekly sheet (B10:E16) and puts it into the yearly sales sheet beside the relevant date in column B.

All help greatly appreciated,

 

[Takae]

This is just a suggestion but coarse code.

Sub ardykav()
Dim wsW As Worksheet, wsY As Worksheet
Dim LastR As Long

Set wsW = Sheets("Weekly Sales")
Set wsY = Sheets("Yearly Sales")

LastR = wsY.Cells(Rows.Count, 2).End(xlUp).Row

With wsW
    wsY.Range(wsY.Cells(LastR + 1, 2), wsY.Cells(LastR + 7, 5)).Value = .Range("B10:E16").Value
    .Range("B10:E16").ClearContents
End With

End Sub