Excel copy and pasting depending on date range / input of date

S

Stonefield

New Member
Joined
Feb 10, 2018
Messages
2
Hi,

I really hope someone is able to help me out. I have been looking for ways to resolve this excel challenge :)

I have an excel file with 3 worksheets (Overview, Input Data and Current Candidates on Project)
Overview is simple. It shows dates for the year, and start date on the left side. End date on the right side. Overviews should be depending on Input Data.

In the sheet Overview running totals is calculated by the sum of running totals by the previous day added by sale value added on the current day deducted with sale value of the candidates with an end date.

Overview
[TABLE="width: 500"]
<tbody>[TR]
[TD]Startdate[/TD]
[TD]Running totals[/TD]
[TD]Client[/TD]
[TD]Candidate name[/TD]
[TD]Sale Value[/TD]
[TD][/TD]
[TD]Enddate[/TD]
[TD]Client[/TD]
[TD]Running totals[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD]1-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Input Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Client[/TD]
[TD]Candidate Name[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD]5-1-2018[/TD]
[TD]15-2-2018[/TD]
[TD]Test1[/TD]
[TD]John[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]10-1-2018[/TD]
[TD]28-2-2018[/TD]
[TD]Test2[/TD]
[TD]John2[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]5-1-2018[/TD]
[TD]15-2-2018[/TD]
[TD]Test3[/TD]
[TD]John3[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Current Candidates on Project
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Client[/TD]
[TD]Candidate Name[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Question
Would it be possible to create a VBA script or any other solutions that would copy and paste data from Input Data to Sheet Overview and paste it to the corresponding data in the field depending on the data I fill in the sheet Input Date?

So if a candidate would start on 5-1-2018 and end on 15-2-2018 the corresponding data should go on Input Data on the 5th of jan as a stating candidate and at the same time added on the right on the 15th of feb as a ending candidate.

Next to the pasting there is one other challenge. What if there is already data on that date? Ideally I would line to insert a new row and add the data underneath it.

The last challenge is to also paste the candidates that are working TODAY should be displaced on the sheet Current Candidates on Project

Is something like this possible at all?

Your help is very much appreciated. I am stuck with finding a solutions.

Regards,

Rutger
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
What appears on the Sheet InputData is 100% Input - No changes are made to it from the proposed Macro; Right?

The Overview Sheet AND the Current Candidates on Project ARE BOTH updated from the Proposed Macro; Right?

Show us by filling in to the two updatable sheets Overview & CConP the Info as it shoud appear AFTER Running the Macro!!

Regards,

Jim
 
Upvote 0
jim may said:
What appears on the Sheet InputData is 100% Input - No changes are made to it from the proposed Macro; Right?

The Overview Sheet AND the Current Candidates on Project ARE BOTH updated from the Proposed Macro; Right?

Show us by filling in to the two updatable sheets Overview & CConP the Info as it shoud appear AFTER Running the Macro!!

Regards,

Jim
Click to expand...

Hi Jim,

Thank you for replying so quickly. Great that you are willing to have a look.
The Input Data is indeed 100% input. No changes to be made.
Overview and Current Candidates are both updated from the proposed macro.

Below is the ideal situation with first the Input Data field, then the Overview and Current Candidates.
I added Employee name and input data to calculate sale value.

Overview
(sale value = ((margin*hours per week)-(Shrinkage*margin*hours per week)

[TABLE="width: 956"]
<colgroup><col span="2"><col><col><col span="4"><col><col><col></colgroup><tbody>[TR]
[TD]Startdate[/TD]
[TD]Enddate[/TD]
[TD]Employee name[/TD]
[TD]Client[/TD]
[TD]Candidate name[/TD]
[TD]Bill[/TD]
[TD]Pay[/TD]
[TD]Margin[/TD]
[TD]Hours per week[/TD]
[TD]Shrinkage (%)[/TD]
[TD]Sale value[/TD]
[/TR]
[TR]
[TD="align: right"]3-1-2018[/TD]
[TD="align: right"]4-1-2018[/TD]
[TD]abc[/TD]
[TD]Test1[/TD]
[TD]John[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15,00%[/TD]
[TD="align: right"]170[/TD]
[/TR]
[TR]
[TD="align: right"]5-1-2018[/TD]
[TD="align: right"]7-1-2018[/TD]
[TD]def[/TD]
[TD]Test2[/TD]
[TD]John2[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15,00%[/TD]
[TD="align: right"]340[/TD]
[/TR]
[TR]
[TD="align: right"]6-1-2018[/TD]
[TD="align: right"]9-1-2018[/TD]
[TD]def[/TD]
[TD]Test3[/TD]
[TD]John3[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]67,5[/TD]
[TD="align: right"]12,5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15,00%[/TD]
[TD="align: right"]425[/TD]
[/TR]
[TR]
[TD="align: right"]5-1-2018[/TD]
[TD="align: right"]7-1-2018[/TD]
[TD]ghj[/TD]
[TD]Test4[/TD]
[TD]John4[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]52,5[/TD]
[TD="align: right"]7,5[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15,00%[/TD]
[TD="align: right"]255[/TD]
[/TR]
</tbody>[/TABLE]

Overview
[TABLE="width: 1032"]
<colgroup><col><col span="2"><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Startdate[/TD]
[TD]Running totals[/TD]
[TD]Employee name[/TD]
[TD]Client[/TD]
[TD]Candidate name[/TD]
[TD]Sale Value[/TD]
[TD][/TD]
[TD]Enddate[/TD]
[TD]Employee name[/TD]
[TD]Client[/TD]
[TD]Candidate name[/TD]
[TD]Sale Value[/TD]
[/TR]
[TR]
[TD="align: right"]1-1-2018[/TD]
[TD="align: right"]€ 0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-1-2018[/TD]
[TD="align: right"]€ 0,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3-1-2018[/TD]
[TD="align: right"]€ 170,00[/TD]
[TD]abc[/TD]
[TD]Test1[/TD]
[TD]John[/TD]
[TD="align: right"]170[/TD]
[TD][/TD]
[TD="align: right"]3-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4-1-2018[/TD]
[TD="align: right"]€ 170,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4-1-2018[/TD]
[TD]abc[/TD]
[TD]Test1[/TD]
[TD]John[/TD]
[TD="align: right"]170[/TD]
[/TR]
[TR]
[TD="align: right"]5-1-2018[/TD]
[TD="align: right"]€ 595,00[/TD]
[TD]def[/TD]
[TD]Test2[/TD]
[TD]John2[/TD]
[TD="align: right"]340[/TD]
[TD][/TD]
[TD="align: right"]5-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ghj[/TD]
[TD]Test4[/TD]
[TD]John4[/TD]
[TD="align: right"]255[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6-1-2018[/TD]
[TD="align: right"]€ 1.020,00[/TD]
[TD]def[/TD]
[TD]Test3[/TD]
[TD]John3[/TD]
[TD="align: right"]425[/TD]
[TD][/TD]
[TD="align: right"]6-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7-1-2018[/TD]
[TD="align: right"]€ 1.020,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7-1-2018[/TD]
[TD]def[/TD]
[TD]Test2[/TD]
[TD]John2[/TD]
[TD="align: right"]340[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ghj[/TD]
[TD]Test4[/TD]
[TD]John4[/TD]
[TD="align: right"]255[/TD]
[/TR]
[TR]
[TD="align: right"]8-1-2018[/TD]
[TD="align: right"]€ 680,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9-1-2018[/TD]
[TD="align: right"]€ 680,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9-1-2018[/TD]
[TD]def[/TD]
[TD]Test3[/TD]
[TD]John3[/TD]
[TD="align: right"]425[/TD]
[/TR]
[TR]
[TD="align: right"]10-1-2018[/TD]
[TD="align: right"]€ 255,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-1-2018[/TD]
[TD="align: right"]€ 255,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12-1-2018[/TD]
[TD="align: right"]€ 255,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12-1-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Current candidates on project
[TABLE="width: 577"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date Today[/TD]
[TD]Startdate[/TD]
[TD]Enddate[/TD]
[TD]Employee name[/TD]
[TD]Client[/TD]
[TD]Candidate name[/TD]
[TD]Sale Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6-1-2018[/TD]
[TD="align: right"]5-1-2018[/TD]
[TD="align: right"]7-1-2018[/TD]
[TD]def[/TD]
[TD]Test2[/TD]
[TD]John2[/TD]
[TD="align: right"]340[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6-1-2018[/TD]
[TD="align: right"]9-1-2018[/TD]
[TD]def[/TD]
[TD]Test3[/TD]
[TD]John3[/TD]
[TD="align: right"]425[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5-1-2018[/TD]
[TD="align: right"]7-1-2018[/TD]
[TD]ghj[/TD]
[TD]Test4[/TD]
[TD]John4[/TD]
[TD="align: right"]255[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1020[/TD]
[TD]Total[/TD]
[/TR]
</tbody>[/TABLE]


If I can help in any way please let me know!

Rutger
 
Upvote 0
@Stonefield

Please do not post the same question multiple times. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.

I have deleted your other 2 replies to this thread
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs
K
  • Question Question
Formula to find Data and Corresponding date and return total sales and total units per item
Replies
4
Views
185
RoryA
RoryA
Z
  • Question Question
Sum Sales Values by Dates over different periods
Replies
6
Views
496
RobP
R
Z
  • Question Question
Sum Values by month from 1st day to last day of each month, and specific date range
Replies
6
Views
475
alansidman
A
L
  • Question Question
Selection based on two variables - copy and paste to another sheet
Replies
3
Views
363
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top