Hi everyone,
I would need help to create a macro which could implement the five steps below, however I am not too sure if that is possible at all. Attached is the spreadsheet which contain the 'Staging Jobsheet-Template' worksheet (original data) and the new worksheet (i.e. the outcome following macro).
Explanations of the 5 required steps:
- First step:
1) Add a new worksheet
- Second step:
2) In the 'Staging Jobsheet-Template' worksheet, search for the first non-empty cell in column D (this will be the first row of relevant data)
3) Starting from the row where the first non-empty cell was found (in this case row 16), copy columns A, c and D of the 'Staging Jobsheet-Template' worksheet respectively to columns D, F and E of the new worksheet when the following conditions are met:
a) Cell from column A is never emtpy
b) Cell in column C is equal to a number between 1 and 10
- Third step:
4) Search for content of cell E1 from the new worksheet in the 'Serial No.' row of the 'Staging Jobsheet-Template' worksheet (in this case FCZ150475AA)
5) Once the value has been found, copy the associated Temporary Device Name (i.e. the cell located 8 rows above the cell found in previous step), in this case VPN22457R1
6) Paste the value in the cell located 2 columns to the left of E1 in the new worksheet (in this case cell C1 of the new worksheet)
7) Carry on step 4 through 6 for all values present in column E of the new worksheet
- Fourth step:
8) In the 'Staging Jobsheet-Template' worksheet, search for cell entitled 'Site ID' in column A
9) Locate and copy the first cell located to the right of the 'Site ID' cell (in this case copy value 11111)
10) Paste the value in column B of the new worksheet for all rows having a value in column D
- Fifth step:
11) In the 'Staging Jobsheet-Template' worksheet, search for cell entitled 'Site Address' in column A
12) Locate the first cell located to the right of the 'Site Address' cell (in this case 201-203 MAN,BRADGATE,BP9 4RT,ENGLAND)
13) Paste the value in column J of the new worksheet for all rows having a value in column D
Thanks a lot for your help,
Antoine
Bookmarks