Hi
I thought maybe it makes sense to almost like talk through the first 2 cycles/loops of how I imagine how the code would work (example is as per the file attached on first post).
Lets use the below as an example. Note that this is just 1 sheet out of the whole 9 sheets.
1) In the beginning the code decides on the scan range which is Sheets("Data_shift"). Range("I2:IV(last row as per column F)"). Maybe something as per below:
2)It then starts to check the sheets cell by cell
3)If cell (I2 - in GREEN) is blank then do nothing and loop/move to the next cell (i.e. J2). If cell (I2) is not blank then remember the reference cell location (i.e. R2C9) and then proceed.
4)Using the reference cell location (i.e. R2C9) the code will use the row number (i.e 2) and take information from sheet "Data_shift" column B to column F (i.e. R2C2:R2C6) and store this in the memory. Example - the blue selection below is copied and stored in memory.
5) Now the code will find the first available blank cell on sheet "Data_compiled" using col B as a reference. In our example the first row is used for headers, so first available cell is column B Row 2 (i.e. R2C2).
6) The code would then paste the previously obtained information (Blue section as per point 4) to column B to column F by using the obtained first available row as (see point 5) the reference. So in our example it would paste the info to Sheet "Data_compiled" R2C2:R2C6 so the outcome would be as below:
7)Then the code would start to copy the reference cell I2 (i.e. R2C9) of each sheet (all those 9 sheets) and paste it to the sheet "Data_compiled" to relevant hard coded column as per below.
CONTINUES ON NEXT POST
Bookmarks