VBA: How to continue a pattern on a newly created sheet.

[Peter h]

Hey guys. I got a question, and hopefully it isn't too difficult. I just can't seem to think of a way to do what I'm wanting to do. I'm creating a work schedule for my employees, and want to create a macro that I can run that will create a new sheet named whatever the next year is, and then formatted the same as the one already made. So, basically the new sheet will look exactly like the previous, but be updated with that year's dates. All of that is simple enough, and I can figure that out. My problem is that our work schedule is not a normal mon-fri 9-5 schedule. I've got 4 crews and all of them follow the same 28 day rotation, but on different days. So, in column "A" I have dates (Jan. 1 - Dec. 31), in columns "B"-"E" I have the schedule. The pattern is: DDD O NNN OOO DDDD OOOOOOO NNNN OOO (D = Day, N = Night, and O = Off)

So, is there a way that when a macro creates the new sheet for the new year, it can automatically continue the pattern in Row 1 where the last sheet left off?

 

[shg]

Why not just use one sheet and hide (or filter) all but the relevant rows?

 

[TonyUK72]

In column A you have your dates, what you need to do is populate range B1 to E28 with the current days your employees will be working.

Now in cell B29 type =B2 and copy and paste B29 into C29 to E29

Now copy and paste B29 to E29 down to B57 to E57

This will give you your 2nd batch of shift patterns, you can then copy and paste this batch down your spreadsheet.

Not a perfect solution but a quick one.

 

[Peter h]

Why not just use one sheet and hide (or filter) all but the relevant rows?

I could do that. I just thought it might be cleaner and more organized if I just created a new sheet for the new year's schedule. Your solution may end up being what I go with if I don't think of another way to do it.

In column A you have your dates, what you need to do is populate range B1 to E28 with the current days your employees will be working.

Now in cell B29 type =B2 and copy and paste B29 into C29 to E29

Now copy and paste B29 to E29 down to B57 to E57

This will give you your 2nd batch of shift patterns, you can then copy and paste this batch down your spreadsheet.

Not a perfect solution but a quick one.

Ya, I know how to manually repeat the pattern. I was looking for a way to automatically continue the pattern in a new sheet with a macro. Thanks though.