Copy data from one range to another with dynamic criteria

marillionnut

New Member
Joined
Feb 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Screenshot 2021-03-01 at 16.33.24.jpg
    Screenshot 2021-03-01 at 16.33.24.jpg
    144.3 KB · Views: 5
  • Screenshot 2021-03-01 at 16.35.32.jpg
    Screenshot 2021-03-01 at 16.35.32.jpg
    110.4 KB · Views: 5

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,774
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Feb 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,774
Office Version
  1. 365
Platform
  1. Windows
Oops it should be G3 in the code not K3, if you change that it should be ok.
 
Solution

marillionnut

New Member
Joined
Feb 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff - that works perfectly, thank you for your help.

Happiness is the Road!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,774
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,995
Messages
5,639,446
Members
417,090
Latest member
schoelleya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top