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

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
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?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Why not just use one sheet and hide (or filter) all but the relevant rows?
 

TonyUK72

Board Regular
Joined
Oct 6, 2015
Messages
220
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.
 
Last edited:

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,263
Messages
5,623,704
Members
415,983
Latest member
MusicMan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top