VBA - Copy Rows to new worksheet based on criteria (1 Viewer)

[ggtrixabell]

Hi,
I am very new to VBA and have been struggling to find everything I need in one place to complete a task I'd like to do. Any help would be much appreciated.

I have a risk register which captures threats and issues that may occur within my projects. The outcome of those could be a task or an opportunity. I would like to be able to have the team click a button which then runs a macro to look at any risks or issues that have an outcome as 'TASK' and then copy the details to a separate worksheet in my workbook so that the task table can be progressed away from the main capture table. The same goes for any opportunities.
The data would need to be copied and then have the values only pasted to the relevant table in the other 2 worksheets because I have formulas working out the reference number for each new threat/issue in the main table.
My data table on Sheet1 looks like this: Sorry, it didn't copy properly..

RefProject CodeProject NameProject LeadStatusDate Raised TYPERaised ByDescriptionImpactProximity #1 (Department)Proximity #2 (Department)Proximity (Timeline)COSTREVENUEQUALITYTIMEFRAMEAbsolute Probability Absolute Impact
Risk ScoreRisk LevelOutcomeAssigned To Action / UpdatesStatusApproved ByApproval DateCompletion Date NotesDocument Ref NoCHRL-001CHRLChocolate RollJDCLOSED12-Dec-16THREATChoc Supplier lostetcetcetcetcetcetcetcetcetcetcetcetcTASKsupply ChainAlternative supplier neededetcetcetcetcetcetcPNCH-002PNCHPeanut Chip CookieJDCLOSED12-Dec-16THREATRival company openedetcetcetcetcetcetcetcetcetcetcetcetcOpportunityMarketingMarket research - Possible commercial agreementetcetcetcetcetcetc


When the activex button is pressed, I'd like the macro to look for any new "TASK" or "Opportunity" (In column V) that has been added since the last time it was run and to then copy the data in that row but only from cells in columns AtoI and WtoX if possible, to the corresponding worksheet - Task or Opportunity..

I've managed to get as far as identifying the task or opportunity and copying the row onto the correct worksheet but that's it..

Sorry if this is a complicated explanation, my first time posting too...Any help much appreciated.

 

[Minty]

You can post up a excel sheet or sample database if you zip it. I can't work out from your posting what your data looks like.

Ideally we would need sample data, enough to cover all outcomes, and the expected output.

 

[ggtrixabell]

Hi Minty,
Thank you for your quick reply. I'm trying to upload a sample for you but I don't have that option. I don't think my subscription/status allows it.

I'll keep trying..

 

[Minty]

You will need to zip it, and use the advanced editor. (Go Advanced below the reply box)

 

[ggtrixabell]

Ta-Da! Thank you, I managed it...

 

[Minty]

Is the spreadsheet data the same layout as your table data is stored?
What determines "the last time it was run" - What criteria do you use to determine that ?

 

[ggtrixabell]

Hi,
Yes, it is the same lay out.
I don't know how to determine the bit for working out the last time it was run... I was thinking maybe a lookup to see if the ref number in column A had already been copied over? Or even to just copy everything over and re-write what is already there each time if there's no way to determine it? As long as the 'updates' from columns M onwards stay on the Task and the Opp tabs and don't get written over, the data in columns that get copied over from the main table should never change anyway. So I suppose it doesn't need to work out when the last time it was run... I just talked myself out of that bit

 

[arnelgp]

please take a look.
not pretty but will do it.

 

[Minty]

If the Updates tab represents another Access table then you need to look at how you are storing your data, as that layout is not normalised, and will give you lots of problems moving forwards.

To keep track of multiple updates / entries to any data you need to think vertically, not horizontally. So to use your current data as an example;

[B]tblUpdates[/B]
[I]UpdateID          RefNo         DateUpdated[/I]
1                CHRL-001     01/09/2107      
2                PNCH-002     02/09/2107
3                CHRL-001     04/09/2017
4                CHRL-001     13/09/2017
5                PNCH-002     14/09/2017

It is now simple to find the last date for any reference number using a group query and selecting the Max(DateUpdated)

 

[arnelgp]

please take a look.
no button, goto ribbon Add-Ins.

 

[ggtrixabell]

please take a look.
not pretty but will do it.

Wow!
Thank you very much indeed!!

 

[ggtrixabell]

If the Updates tab represents another Access table then you need to look at how you are storing your data, as that layout is not normalised, and will give you lots of problems moving forwards.

To keep track of multiple updates / entries to any data you need to think vertically, not horizontally. So to use your current data as an example;

[B]tblUpdates[/B]
[I]UpdateID          RefNo         DateUpdated[/I]
1                CHRL-001     01/09/2107      
2                PNCH-002     02/09/2107
3                CHRL-001     04/09/2017
4                CHRL-001     13/09/2017
5                PNCH-002     14/09/2017

It is now simple to find the last date for any reference number using a group query and selecting the Max(DateUpdated)

Thank you very much for your time and advice, much appreciated