Macro Required to copy information from one sheet to a specific location in another - based on criteria

J

JANGLE

New Member
Joined
Aug 28, 2017
Messages
1
Hi,

This is my first time posting, so I apologise if I don't provide enough information. I'm uncertain if this will be possible with VBA/ Excel but it's worth a shot.

I am looking for a Macro that would look into one sheet and update/ populate a second sheet based on values.

here is what I'm trying to do;

1. Sheet2 is updated for "new projects", and existing tenders are updated for "Probability" as we come closer to due dates.
[TABLE="width: 1706"]
<tbody>[TR]
[TD]HIGH FOCUS PROJECTS [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PROECT NUMBER[/TD]
[TD]PROJECT NAME[/TD]
[TD]Probability[/TD]
[TD]Project Details[/TD]
[TD]Notes[/TD]
[TD]Location[/TD]
[TD]Tender Expected[/TD]
[TD]Expected Start Date[/TD]
[TD]Estimated End Date[/TD]
[TD]Estimated Value ($M)[/TD]
[/TR]
[TR]
[TD]Urban[/TD]
[TD="align: right"]33[/TD]
[TD]Project 33[/TD]
[TD="align: right"]5%[/TD]
[TD]AAA[/TD]
[TD]XXX[/TD]
[TD]Location 1[/TD]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Feb-21[/TD]
[TD="align: right"]1,000[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD="align: right"]34[/TD]
[TD]Project 34[/TD]
[TD="align: right"]50%[/TD]
[TD]BBB[/TD]
[TD]XXX[/TD]
[TD]Location 2[/TD]
[TD] [/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]2,000[/TD]
[/TR]
[TR]
[TD]Infrustructure[/TD]
[TD="align: right"]35[/TD]
[TD]Project 35[/TD]
[TD="align: right"]25%[/TD]
[TD]CCC[/TD]
[TD]XXX[/TD]
[TD]Location 3[/TD]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]3,000[/TD]
[/TR]
[TR]
[TD]INVITED/ADVERTISED TENDERS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PROECT NUMBER[/TD]
[TD]PROJECT NAME[/TD]
[TD]Probability[/TD]
[TD]Project Details[/TD]
[TD]Notes[/TD]
[TD]Location[/TD]
[TD]Tender Close Date[/TD]
[TD]Expected Start Date[/TD]
[TD]Estimated End Date[/TD]
[TD]Estimated Value ($M)[/TD]
[/TR]
[TR]
[TD]Urban[/TD]
[TD="align: right"]41[/TD]
[TD]Project 41[/TD]
[TD="align: right"]20%[/TD]
[TD]DDD[/TD]
[TD]XXX[/TD]
[TD]Location 4[/TD]
[TD="align: right"]25-Sep-17[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD="align: right"]42[/TD]
[TD]Project 42[/TD]
[TD="align: right"]20%[/TD]
[TD]EEE[/TD]
[TD]XXX[/TD]
[TD]Location 5[/TD]
[TD="align: right"]15-Sep-17[/TD]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]CURRENT TENDERS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PROECT NUMBER[/TD]
[TD]PROJECT NAME[/TD]
[TD]Probability[/TD]
[TD]Project Details[/TD]
[TD]Notes[/TD]
[TD]Location[/TD]
[TD]Tender Close Date[/TD]
[TD]Expected Start Date[/TD]
[TD]Estimated End Date[/TD]
[TD]Estimated Value ($)[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD="align: right"]51[/TD]
[TD]Project 51[/TD]
[TD="align: right"]50%[/TD]
[TD]FFF[/TD]
[TD]XXX[/TD]
[TD]Location 6[/TD]
[TD]TBA[/TD]
[TD]TBA[/TD]
[TD]TBA[/TD]
[TD="align: right"]6,000[/TD]
[/TR]
[TR]
[TD]Infrastructure[/TD]
[TD="align: right"]52[/TD]
[TD]Project 52[/TD]
[TD="align: right"]70%[/TD]
[TD]GGG[/TD]
[TD]XXX[/TD]
[TD]Location 7[/TD]
[TD]Mid Aug 17[/TD]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]7,000[/TD]
[/TR]
[TR]
[TD]TENDERS PENDING[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PROECT NUMBER[/TD]
[TD][/TD]
[TD]Probability[/TD]
[TD]Project Details[/TD]
[TD]Notes[/TD]
[TD]Location[/TD]
[TD]Tender Submitted[/TD]
[TD]Expected Start Date[/TD]
[TD]Estimated End Date[/TD]
[TD]Tendered Value[/TD]
[/TR]
[TR]
[TD]Urban[/TD]
[TD="align: right"]61[/TD]
[TD]Project 61[/TD]
[TD="align: right"]10%[/TD]
[TD]HHH[/TD]
[TD]XXX[/TD]
[TD]Location 8[/TD]
[TD="align: right"]18-Aug-17[/TD]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]8,000[/TD]
[/TR]
[TR]
[TD]Urban[/TD]
[TD="align: right"]62[/TD]
[TD]Project 62[/TD]
[TD="align: right"]15%[/TD]
[TD]III[/TD]
[TD]XXX[/TD]
[TD]Location 9[/TD]
[TD="align: right"]11-Aug-17[/TD]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]9,000[/TD]
[/TR]
</tbody><colgroup><col span="6"><col><col><col><col><col></colgroup>[/TABLE]



2. I then need the following to be moved into sheet 1.
a. The macro to determine if any Probability percentages have been changed on existing projects and if so, update probability percentage
b. The macro to determine if new projects exist in the sheet2 list when compared specifically against the "Unsecured" - "Revenue by full contract value" section in blue only
c. If new projects exist, the macro to insert the new projects after either D16 (i.e. start at D17 for Infrastructure projects), or start after D18 (i.e. start in D19) for Urban Projects. These locations will need to change each time the sheet is updated due to this being an expanding list.
[TABLE="width: 1786"]
<tbody>[TR]
[TD]Secured/ Unsecured[/TD]
[TD]Forecast[/TD]
[TD]Sector[/TD]
[TD]Project #[/TD]
[TD]Project Name[/TD]
[TD]Opportunity[/TD]
[TD]Contract Value Total[/TD]
[TD]Forecast Margin[/TD]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]May-17[/TD]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]Oct-17[/TD]
[/TR]
[TR]
[TD]SECURED[/TD]
[TD]FORECAST REVENUE[/TD]
[TD]URBAN[/TD]
[TD="align: right"]12[/TD]
[TD]Project 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"] $1,000.00 [/TD]
[TD="align: right"] $2,000.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD]Project 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]INFRASTRUSTURE[/TD]
[TD="align: right"]15[/TD]
[TD]Project 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD]Project 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORECAST COST[/TD]
[TD]URBAN[/TD]
[TD="align: right"]12[/TD]
[TD]Project 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD]Project 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]INFRASTRUSTURE[/TD]
[TD="align: right"]15[/TD]
[TD]Project 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD]Project 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORECAST MARGIN[/TD]
[TD]URBAN[/TD]
[TD="align: right"]12[/TD]
[TD]Project 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"] $900.00 [/TD]
[TD="align: right"] $1,900.00 [/TD]
[TD="align: right"]-$100.00 [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD]Project 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-$100.00 [/TD]
[TD="align: right"]-$100.00 [/TD]
[TD="align: right"]-$100.00 [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[TD="align: right"] $- [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]INFRASTRUSTURE[/TD]
[TD="align: right"]15[/TD]
[TD]Project 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD]Project 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$150.00[/TD]
[TD="align: right"]$250.00[/TD]
[/TR]
[TR]
[TD]UNSECURED[/TD]
[TD]REVENUE by Full Contract Value[/TD]
[TD]INFRASTRUSTURE[/TD]
[TD]30[/TD]
[TD]Project 30[/TD]
[TD]75%[/TD]
[TD] $10,000.00 [/TD]
[TD]4000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"] $1,000.00 [/TD]
[TD="align: right"] $1,000.00 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31[/TD]
[TD]Project 31[/TD]
[TD]10%[/TD]
[TD] $20,000.00 [/TD]
[TD]10200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]32[/TD]
[TD]Project 32[/TD]
[TD]20%[/TD]
[TD] $30,000.00 [/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]URBAN[/TD]
[TD]21[/TD]
[TD]Project 21[/TD]
[TD]10%[/TD]
[TD] $40,000.00 [/TD]
[TD]37000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"] $1,000.00 [/TD]
[TD="align: right"] $1,000.00 [/TD]
[TD="align: right"] $1,000.00 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]Project 22[/TD]
[TD]15%[/TD]
[TD] $50,000.00 [/TD]
[TD]42000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"] $2,000.00 [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col span="5"><col span="2"><col><col span="2"></colgroup>[/TABLE]




Again, not entirely sure if this will be possible, but please let me know if anyone has any ideas.

Thank you,
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You must log in or register to reply here.

Similar threads

A
  • Solved
copy data based on highlighted cell in column into lastrow from sheet to another
Replies
2
Views
345
Abdo
A
K
  • Question Question
sort data based on two dates and sheet name from sheet to another
Replies
2
Views
372
Fluff
Fluff
N
  • Solved
VBA auto fill in information from the list in one sheet to another sheet and save as the excel file.
Replies
5
Views
191
ngochien251088
N
M
  • Question Question
Index Match function List based on date
Replies
7
Views
243
Fluff
Fluff
H
  • Question Question
populate average price on listbox based on selected sheet from combobox
Replies
1
Views
313
RoryA
RoryA

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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