Excel VBA shift selected cells in various columns up/down for a specified number cell

[muellerlohse]

Hi all,
it's my fist thread, so my apologize in case something is not fully correct.
I am struggling withmy excel worksheet and hope that somebody can assist me to find a solution.
Basically I am workingin a file with consecutively repeating entry sections to make somecalculations.
As sometimes I will need to shift all sections down in order to enter data inbetween, I would like to create a makro that will copy only the cells with myentries (and the remaining fixed entries will be locked and not changed) and shiftall of them 13 rows down to paste them again in the same pattern as before.Just 13 rows more down. Maybe the attached screenshot will help to visualize.
For example leg 2 mustbe replaced with new input – so the data from leg 2,3,4,…. Must be shifted downto get the input field clear in leg 2. Therefore I want the valued from C17-C18,C30-C31, C43-44, E23, E25, E 36, E37, E49, E50, G15, G17…. Shifted down for 13rows. I didn’t find a suitable solution for me to do this with an offset whichwould be the best option as I will need the function finally for each of thelegs to be able to generate a gap wherever I will need it.
Hoping for yourassistance.
Dennis

[p45cal]

You're experiencing the classic difficulty of having data for humans to read in the same place as the data itself.
Consider separating displayed data from the data you edit.

So in the attached are two examples (still not friendly) where the shaded cells in your picture are linked to the data elsewhere.
In Sheet1 I used INDIRECT formulae to link the shaded cells to a small table in column M:R. I used INDIRECT because if you insert rows in the table the references remain the same.
In Sheet2 I've used a proper Excel Table in columns M:R and linked the shaded cells using INDEX to a column in that table:
=INDEX(Table1[PA],2)
where [PA] is Port of Arrival column and the 2 is the second row.
If you have lots of legs it might get tiresome to enter these row numbers in all the legs, but you can save yourself that by altering the formula a bit to the likes of:
=INDEX(Table1[PA],INT(ROW()/13)+1)
where the INT(ROW()/13)+1 part works out the row of the table to get the data from, from the row number (ROW()) that the formula sits in. (Column T Sheet2 shows that calculation, but you don't need column T)

Anyway, the upshot is you can insert rows into the data tables on either sheet and observe the data in the left columns changing.

ps. it really helps to attach a workbook, it would have saved me a lot of time copying stuff from a picture (and from guessing wrongly what your set up is).

[muellerlohse]

Hi, and many thanks first of all for your swift response. I will check it tonight when I am back on duty. And sorry, you are right, would have been more easy to attach the worksheet itself. I will revert and next time I will attach the file.

[Paul_Hossler]

You're experiencing the classic difficulty of having data for humans to read in the same place as the data itself.
Consider separating displayed data from the data you edit.

Simple concepts in attachment.

1. A 'database' sheet of unchanging values

2. Data validation on Ports

3. Formulas for calculatable values

4. A data input sheet (the formula cells [headers in italics] really not needed on the data input, but could just be in the report sheet)

Capture.JPG


5. The report sheet could be built off of the data input sheet, most probably using a macro to do the formatting, etc.