Rotate List of names bottom to top Repeat without condition with option to set a condition

[spyac]

Hello,
I have a list of employees in Column A with a manual entry of % of work for the day in Column B. As an example Row 1 Column A&B should move together. There are headers in Column A&B with 10 employees currently. The current range is A1:B11. I would like to rotate the names in columnA plus any entry in column B bottom to top with the top moving down one row. I would like to repeat this each time the macro is selected, but add an option that may allow me to set a condition, such as only once a day if i need to in the future. Until we see how the workload is dispersed I am undecided on adding in the condition. I did try adapting code I found online as i tried to work through this, but it fails at "NewListValue(Placement(Counter), 1) = _" with subscript out of range. The code below was setup only for Column A, but did not work. I do still need A&B to move together. The code below was just a start to the solution. There may be better ways to appraoch this.

Please Login or Register  to view this content.

[Marc L]

Hi !

With data from a worksheet no needs any loop …

[JLGWhiz]

You could use event code like that below to rotate the data in columns A and B beginning at row 2. If you want to restrict the rotation to once a day, then remove the apostrophe on line 2 of the code.

Please Login or Register  to view this content.

The code would be placed in the sheet code module for the sheet where the rotation is desired.

[spyac]

Hi JL GWhiz,
The code does work on the sheet. I have adjacent formulas that all get corrupted when the rotation occurs. I was able to adjust the date down a cell that eliminated it writing over another cell, but when the Cut or Shift down occurs, the formulas in adjacent cells change. Even though they are absolute they change. I am not familiar with the reize command. I actually have 4 of these that I need to rotate on the same sheet. Is there an alternate way to run this. Possibly in a module compared to sheet code?
Schedule 1 Columns A&B rotate
Schedule2 Columns G&H rotate
Schedule 3 Columns M&N rotate
Schedule 4 Columns S&T rotate
All exist in Rows 2-11
I can repeat the code with the new columns identified and I was able to get the first 2 to rotate, but both had the formula error then
Thank You in Advance

[JLGWhiz]

Hi JL GWhiz,
The code does work on the sheet. I have adjacent formulas that all get corrupted when the rotation occurs. I was able to adjust the date down a cell that eliminated it writing over another cell, but when the Cut or Shift down occurs, the formulas in adjacent cells change. Even though they are absolute they change. I am not familiar with the reize command. I actually have 4 of these that I need to rotate on the same sheet. Is there an alternate way to run this. Possibly in a module compared to sheet code?
Schedule 1 Columns A&B rotate
Schedule2 Columns G&H rotate
Schedule 3 Columns M&N rotate
Schedule 4 Columns S&T rotate
All exist in Rows 2-11
I can repeat the code with the new columns identified and I was able to get the first 2 to rotate, but both had the formula error then
Thank You in Advance

You did not mention the formulas and their locations in the OP. If any of the cells being rotated are dependent or precedent to the formulas, Excel will attempt to keep the reference to tho the original cell, wherever it moves. Does not matter where the code runs from.

The resize method allows the parameters of an anchor cell or range to be resized into a range of cells using row and column parameters, e.g. Resize(2, 6) would be 2 rows and six columns starting with the anchor cell at upper left. It can save a lot of space from writing out the Range and Cells syntax.