Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA ?
Hi
Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA ?
Try this..to select and copy sheets to a new workbook
Sheets.Select ActiveWindow.SelectedSheets.Copy If you want to specify the workbook please use the below code ActiveWindow.SelectedSheets.Copy.Copy Befo=Workbooks("Workbooktocopy").Sheets(1) If this post helps click Yes --------------- Jacob Skaria "George from Central Trains Birmingham UK" wrote: Hi Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA ?
Oops. It is move..and not copy
'To select and move sheets to a new workbook Sheets.Select ActiveWindow.SelectedSheets.Move 'If you want to specify the workbook please use the below code. Specify the workbook to move to.. ActiveWindow.SelectedSheets.Move Befo=Workbooks("<Workbooktomove").Sheets(1) -- If this post helps click Yes --------------- Jacob Skaria "George from Central Trains Birmingham UK" wrote: Hi Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA ?
Hi George
You can move an array of sheets as a group. Look at this: Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Move After:=Workbooks( _ "Book1").Sheets(sheets.Count) Do you turn off screen updating before you macro is moving sheets ? Application.ScreenUpdating = False To turn it on again: =True Hopes this helps. --- Per "George from Central Trains Birmingham UK" icrosoft.com skrev i meddelelsen ... Hi Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA
You can select the required sheets using a loop..before moving
-- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Oops. It is move..and not copy 'To select and move sheets to a new workbook Sheets.Select ActiveWindow.SelectedSheets.Move 'If you want to specify the workbook please use the below code. Specify the workbook to move to.. ActiveWindow.SelectedSheets.Move Befo=Workbooks("<Workbooktomove").Sheets(1) -- If this post helps click Yes --------------- Jacob Skaria "George from Central Trains Birmingham UK" wrote: Hi Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA
Thanks Very Much Jacob
This will work fine I can select all the sheets that require moving via a Do..Loop then use the activewindow.selectedsheets function to transfer in one go which is more efficient and user friendly Thanks again George "Jacob Skaria" wrote: You can select the required sheets using a loop..before moving -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Oops. It is move..and not copy 'To select and move sheets to a new workbook Sheets.Select ActiveWindow.SelectedSheets.Move 'If you want to specify the workbook please use the below code. Specify the workbook to move to.. ActiveWindow.SelectedSheets.Move Befo=Workbooks("<Workbooktomove").Sheets(1) -- If this post helps click Yes --------------- Jacob Skaria "George from Central Trains Birmingham UK" wrote: Hi Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA
Thanks for your response Per
Unfortunately the array function doesn't work in my situation because the names of the individual sheets are dynamic ie they change from week to week. The array option in VBA only works when the sheet names do not vary and they can then be hard coded I got an additional response from Jacob Skaria which will solve my problem. I am now kicking myself that I didn't think of the option he suggested Thanks a lot for taking the time to read and respond to my question Best wishes George "Per Jessen" wrote: Hi George You can move an array of sheets as a group. Look at this: Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Move After:=Workbooks( _ "Book1").Sheets(sheets.Count) Do you turn off screen updating before you macro is moving sheets ? Application.ScreenUpdating = False To turn it on again: =True Hopes this helps. --- Per "George from Central Trains Birmingham UK" icrosoft.com skrev i meddelelsen ... Hi Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I move multiple Worksheets between Workbooks using VBA
Cheers mate..
If this post helps click Yes --------------- Jacob Skaria "George from Central Trains Birmingham UK" wrote: Thanks Very Much Jacob This will work fine I can select all the sheets that require moving via a Do..Loop then use the activewindow.selectedsheets function to transfer in one go which is more efficient and user friendly Thanks again George "Jacob Skaria" wrote: You can select the required sheets using a loop..before moving -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Oops. It is move..and not copy 'To select and move sheets to a new workbook Sheets.Select ActiveWindow.SelectedSheets.Move 'If you want to specify the workbook please use the below code. Specify the workbook to move to.. ActiveWindow.SelectedSheets.Move Befo=Workbooks("<Workbooktomove").Sheets(1) -- If this post helps click Yes --------------- Jacob Skaria "George from Central Trains Birmingham UK" wrote: Hi Can anyone help with an Excel 2002 problem ? I am trying to get a workbook which usually has 500+ worksheets to automatically move groups of sheets to other workbooks when required for archiving etc The only way I have managed this in VBA is by moving one sheet at a time within a do....loop which is a bit inefficient and does not make good viewing for the user The help pages do not list any way of moving even 2 pages at the same time using VBA Thanks in anticipation George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I move multiple tabs in multiple workbooks to a master? | Excel Worksheet Functions | |||
Can i move multiple lines between workbooks | New Users to Excel | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Move worksheets into separate workbooks | New Users to Excel | |||
How can I move multiple linked workbooks between computers | Excel Discussion (Misc queries) |