We store staff rosters in weekly workbooks, each worksheet within a book represents a day of the week.
<o
> </o>
I have to complete the weekly roster for 4 teams with ten staff (max) in each team, the templates must be filled up to eight weeks in advance. Basic example of the layout (in reality its not just split into 2 hour slots)
<o> </o>
<o> http://www.dropbox.com/s/l3uq0bgte6ymwhr/Template to move.xls</o>
<o> </o>
Last week some changes were made to the template we use, I have been asked to transfer all the planning info into the new templates, it means lots of copying and pasting plus there is always the risk that the template may change again in the future.
<o> </o>
Over the last coupe of weeks I suspected we may be asked to move data so tried to come up with a way of transferring everything out of a weekly book and into a new weekly book template. My solution was:
<o> </o>
Ask how many teams there are now
<o> </o>
Use an input box to define the range areas that hold the following:
Staff names for team one
Working patterns for team one
Staff names for team 2
Working patterns for team 2
Etc
<o> </o>
The data within those ranges can then be moved into a new sheet, which I would call ‘Export’
<o> </o>
The macro would then collect data from the same ranges within the next 4 days of the week worksheets and also paste into the ‘Export sheet’
<o> </o>
Once that was all there I could then manually copy the ‘Export sheets data’ into the new template ona worksheet called ‘Import’
<o> </o>
I would then reverse the macro so it now asked where staff names for team 1 etc begin in the new template then copies the data across from import sheet and back into the new templates days.
<o> </o>
I have been through so many tutorials trying to build this myself. I can do the basics like creating the Import and export sheets, Ive even been able to get a input box asking how many ranges and then listing the ranges I have selected using the inputbox.
<o> </o>
What I cant get into my head is the way I can convert and temporarily store the inputbox ranges and then move all the cell data within those ranges into my Export worksheet. I was thinking I could loop through each worksheet once I know my ranges but I will need to make sure that the paste ranges are places in set areas of the ‘Export sheet’ as I will need to pull on those set areas when I attempt to move the data into the new template.
<o> </o>
Any help appreciated.
<o
> </o>I have to complete the weekly roster for 4 teams with ten staff (max) in each team, the templates must be filled up to eight weeks in advance. Basic example of the layout (in reality its not just split into 2 hour slots)
<o
> </o><o
> http://www.dropbox.com/s/l3uq0bgte6ymwhr/Template to move.xls</o><o
> </o>Last week some changes were made to the template we use, I have been asked to transfer all the planning info into the new templates, it means lots of copying and pasting plus there is always the risk that the template may change again in the future.
<o
> </o>Over the last coupe of weeks I suspected we may be asked to move data so tried to come up with a way of transferring everything out of a weekly book and into a new weekly book template. My solution was:
<o
> </o>Ask how many teams there are now
<o
> </o>Use an input box to define the range areas that hold the following:
Staff names for team one
Working patterns for team one
Staff names for team 2
Working patterns for team 2
Etc
<o
> </o>The data within those ranges can then be moved into a new sheet, which I would call ‘Export’
<o
> </o>The macro would then collect data from the same ranges within the next 4 days of the week worksheets and also paste into the ‘Export sheet’
<o
> </o>Once that was all there I could then manually copy the ‘Export sheets data’ into the new template ona worksheet called ‘Import’
<o
> </o>I would then reverse the macro so it now asked where staff names for team 1 etc begin in the new template then copies the data across from import sheet and back into the new templates days.
<o
> </o>I have been through so many tutorials trying to build this myself. I can do the basics like creating the Import and export sheets, Ive even been able to get a input box asking how many ranges and then listing the ranges I have selected using the inputbox.
<o
> </o>What I cant get into my head is the way I can convert and temporarily store the inputbox ranges and then move all the cell data within those ranges into my Export worksheet. I was thinking I could loop through each worksheet once I know my ranges but I will need to make sure that the paste ranges are places in set areas of the ‘Export sheet’ as I will need to pull on those set areas when I attempt to move the data into the new template.
<o
> </o>Any help appreciated.
