How do I move multiple Worksheets between Workbooks using VBA ?

[George from Central Trains Birmingham UK]

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

 

[Jacob Skaria]

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
Before:=Workbooks("Workbooktocopy").Sheets(1)

If this post helps click Yes

 

[Jacob Skaria]

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
Before:=Workbooks("<Workbooktomove>").Sheets(1)

 

[Per Jessen]

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.

 

[Jacob Skaria]

You can select the required sheets using a loop..before moving

 

[George from Central Trains Birmingham UK]

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

 

[George from Central Trains Birmingham UK]

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

 

[Jacob Skaria]

Cheers mate..

If this post helps click Yes