Move inputbox defined ranges

Jemma1979

New Member
Joined
Nov 29, 2008
Messages
23
We store staff rosters in weekly workbooks, each worksheet within a book represents a day of the week.
<o:p> </o:p>
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:p> </o:p>
<o:p> http://www.dropbox.com/s/l3uq0bgte6ymwhr/Template to move.xls</o:p>
<o:p> </o:p>
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:p> </o:p>
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:p> </o:p>
Ask how many teams there are now
<o:p> </o:p>
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:p> </o:p>
The data within those ranges can then be moved into a new sheet, which I would call ‘Export’
<o:p> </o:p>
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:p> </o:p>
Once that was all there I could then manually copy the ‘Export sheets data’ into the new template ona worksheet called ‘Import’
<o:p> </o:p>
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:p> </o:p>
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:p> </o:p>
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:p> </o:p>
Any help appreciated.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,211,744
Messages
6,103,677
Members
447,876
Latest member
rodqntr

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
Back
Top