This project is to tidy up, debug and optimize the macros ive already written
the majority of the script already works, it just needs tidying up and a couple of tweakes to make it run as below, ive written quite allot of the code myself and it repeats itself quite a bit and is probably quite inefficient..
please see attached folder ‘raw data’ in which all of the workbooks have the same data but it wont be like this when im running the real data this is just for the example.
ive attached a file called what ‘how worksheets should look’ which just shows how the data should look like when the macros have been run on it.
NOTE. not all sheets data is the same length, some sheets will have data that goes to row 40
the macros how they should work :
1. delete all sheets in the workbook that contain the phrase ‘ v ’ in their title and th (please note the spaces either side of the letter v). I need to be able to change it around for later, so that i can delete all sheets that dont contain ‘ v ’
2. starting at row 5 if there is data in the A cell of a the row check the range of that row B:T for any empty cells or cells containing the word ‘SP’ or ‘sp’ if that row contains and empty or SP/sp cell, delete the sheet, if it dosnt move on to the next row that has data in the A column until its done all the rows with data in the A column
[url removed, login to view] at row 5 check the A column for cells containing the phrase ‘N/R’ if a cell contains the phrase clear the associated row
4. clear the content of the U column
5. find the the provider (B4:T4) with the best number (highest) for each runner (A column) and put each persons best provider into the W cell in their row, if there are multiple providers with the best results write seperated by commas - eg. (alpha, beta, charlie...)
6. colour B5:W5 ‘red’. Starting at row 6 find the mean average of each range row B5:T5 containing data and colour code the range B:W depending on bellow :
range < 1.5 * B5:W5 - colour range red
range > 1.5 * B5:W5 - colour range yellow
range > 2.5 * B5:W5 - colour range green
7. starting at row 5 find the A cell that matches A3, and copy that rows W cell into the next free cell in the B column in the workbook ‘Master’, sheet ‘Results’
and copy the A1 into the next available cell column A in workbook - ‘MASTER’, sheet ‘RESULTS’
loop this through all sheets and workbooks in specified folder..
Hope thats clear, if their any questions please ask
3 freelance font une offre moyenne de $6/heure pour ce travail
Hi, Bid from a skilled and experienced VBA developer. Besides being a developer I also have long experience as a user. The task at hand is familiar to me and I have done similar solutions before.