Copy data from one range to another with dynamic criteria

[marillionnut]

The screen shot of the workbook attached produces a weekly report based on activities over the week. At the beginning of the week the respective week is selected from D3 and this feeds formula to total up the activities in based on WEEKNUM to provide a monthly overview, with K3 defining the first working day of the first whole week of the year (I am in the Middle East, so this is a Sunday..).

Currently the team record the activities in the ‘Daily Log’ as they go through the week and then manually transpose the data in L10, L11,L12 etc at the end of the week into the respective column in ‘Weekly Data’ to provide the sum of the respective activities (there are 30 different activities in total), this builds as we go through the year.

To make things simpler and a cleaner looking sheet, I want the team just to either click a button or a formula takes the data from L10, L11, L12 etc in ‘Daily Log’ and copy it to the corresponding week in ‘Weekly Data’, based on the week number selected in D3.

I’ve used some elements of VBA before for a simple data entry into next empty row but I’m at a loss on how to copy based on the defined Week number in G3.

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Fugazi()
   With Sheets("Daily log")
      Sheets("Weekly data").Range("C29:C37").Offset(, .Range("K3").Value).Value = .Range("L10:L18").Value
   End With
End Sub

 

[marillionnut]

Hi & welcome to MrExcel.
How about

Sub Fugazi()
   With Sheets("Daily log")
      Sheets("Weekly data").Range("C29:C37").Offset(, .Range("K3").Value).Value = .Range("L10:L18").Value
   End With
End Sub

Hi Fluff and many thanks for the quick reply.

Sorry to say I'm not getting any joy with this and getting runtime error on 3rd row. I'm trying to figure out, based on the week number in G3 how to take the data from L10:L18 and paste it, into the corresponding week number column in C29:J37

I'm keeping trying to find a solution, so all help gratefully received.

(loved the sub name BTW....)

 

[Fluff]

Oops it should be G3 in the code not K3, if you change that it should be ok.

 

[marillionnut]

Hi Fluff - that works perfectly, thank you for your help.

Happiness is the Road!

 

[Fluff]

You're welcome & thanks for the feedback.