Move/Cut # Sheets from one WorkBook To Another with a Macro?

**e4excel** · 03-10-2011, 02:48 AM

Is it possible to Move/Cut # Sheets from one WorkBook To Another with a Macro?

Dear Forum,

I have a WorkBook with several Sheets of different Savings Accounts with Certain Formulas, however I need to create a Copy of some of the Sheets from this WorkBook to a New WorkBook as Copies with the same Format but as Values without Formulas..

Now I have been able to replicate each Sheet with a Macro however was looking forward to Move these Value Sheets in a New File i.e. in a New WorkBook which can be specified in a Sheet called the Replicator in the Main WorkBook which would contain the Name of the New File or WorkBook to be created and also the No of Sheets to be Moved and also the Name of the specific Sheets to be Moved completely..

Ex:
Main WorkBook - Saving Acct Info "This file has Info of different S/B accts with Formulas and the same no of Sheets as Values"..

Lets say the No of Sheets contained in this WorkBook
Acct I, Acct II, Acct III, Ledger-View,
Acct I L-V, Acct II L-V, Acct III L-V
Replicator

AS mentioned above the Sheets Acct I L-V, Acct II L-V, Acct III L-V need to be Moved completely in a New File which should be created via a Macro from the Sheet Replicator.

In the Sheet Replicator:

A1 - path - WHere the New File needs to be placed
A2 - The Name of the New Excel Sheet
A3 - The No of Sheets to be Moved (In the above case 3 all sheets with "L-V" in the end"
A4 - the Names of the Sheets to be Moved one below the other

Is this possible, please let me know as I am not good in VBA but can follow certain instructions..

Warm Regards
e4excel

**Leith Ross** · 03-10-2011, 05:39 AM

Hello e4excel,

This macro will move the specified sheets into a new workbook, save the workbook using the name and directory specified.

Please Login or Register  to view this content.

**e4excel** · 03-11-2011, 08:48 AM

Dear Leith,

Wow this works really well..
I can save so much time now with this code its really very useful..

I dont know whether it be good starting a New thread or not but I will just ask..

Lets say the No of Sheets contained in this WorkBook
Acct I, Acct II, Acct III, PassBook, Ledger-View,
Acct I PB, Acct II PB, Acct III PB, I added 4 Sheets boldened
Acct I L-V, Acct II L-V, Acct III L-V
Replicator

Now from the Above explanation I only wanted to Move the Sheets with the "PB" & "L-V" in the end and an additional Sheet of Index..

P.S. I was also thinking of TIME-Stamping the Sheet Names such as "Acct I PB DDMMYY hhss" and "Acct I L-V DDMMYY hhss"

And also can I get the Order of it like this as mentioned below for Moving :

Index, Acct I PB, Acct I L-V, Acct II PB, Acct II L-V, Acct III PB, Acct III L-V.

So the First Sheet Moved is Index then Acct I PB, Acct I L-V, Acct II PB, Acct II L-V, Acct III PB, Acct III L-V...

Actually the Sheet does not need to be Moved but Just Copied.... while the remaining Sheets to be Moved Completely...

Warm Regards
e4excel.

**e4excel** · 03-11-2011, 04:10 PM

Dear Leith,

Your code works flawlessly, however there's a new requirement of Copying a New Sheet Index from the Parent WorkBook as Values with the exact same Format, Column Width FOrmatting with certain Merged Cells as Values to the New WorkBook as the First Sheet.

Warm Regards
e4excel

Please if you want I can start a New thread..let me know

**Leith Ross** · 03-11-2011, 04:34 PM

Hello e4excel,

Before I modify the macro, is this the complete list of worksheets that you have posted?

**e4excel** · 03-11-2011, 11:51 PM

Originally Posted by Leith Ross

Hello e4excel,

Before I modify the macro, is this the complete list of worksheets that you have posted?

No, In Actuality there are More than 10 Sheets in the WorkBook but only but only 6 Account Sheets X 2 to be Moved + 1 Index Sheet as of now and might increase later but we have just 6 Accts i.e. Acct I till Acct VI right now..

I need to Copy INDEX sheet in the New WorkBOOK on the extreme Left i.e. First Sheet then the order as mentioned in the List.
INDEX \ Acct I PB DDMMYY hhss\ Acct I LB DDMMYY hhss\ Acct II PB DDMMYY hhss\ Acct II LB DDMMYY hhss\ Acct III PB DDMMYY hhss\ Acct III LB DDMMYY hhss
...so on so forth

I would have 1 PassBook and 1 LedgerBook for Each Account Sheet ( Acct I)
So Like "Acct I PB DDMMYY hhss" and "Acct I LB DDMMYY hhss" for Acct I

P.S. I was also thinking of TIME-Stamping the Sheet Names such as "Acct I PB DDMMYY hhss" and "Acct I LB DDMMYY hhss"

But the Logic would remain the same i.e if the Account Nos increase then there the Sheets to be worked on would be 1 + 2 X No of Accounts..but decided by counting the "PB" and "LB" in the Sheet Names and 1 INDEX sheet always.

Dear Leith,
I was also thinking of Automatically generating the Count of the Sheets in the cell A3 BY Counting all the Sheets in the WorkBook with "PB" and "LB" in their Sheet Names

The List of the Sheets also if possibly could be Linked with the Account Nos present in the Index Sheet and also for whom the "* PB DDMMYY hhss" and "* L-V DDMMYY hhss" are created.

So If the INDEX SHEET has lets say 6 Accounts and there exists a corresponding "PB" and "LB" sheets for these Accounts then we could have the List as follows:
Col B of the Index Sheet has the following
Account Numbers
Acct I
Acct II
Acct III
Acct IV
Acct V
Acct VI

So, then the List in the Replicator Sheet can be Auto-Generated (if possible) as this:
Acct I PB + Time Stamp
Acct I LB + Time Stamp
Acct II PB
Acct II LB
Acct III PB
Acct III LB
Acct IV PB
Acct IV LB
Acct V PB
Acct V LB
Acct VI PB
Acct VI LB

The "PB" and "LB" suffixes are fixed but the TIME STAMP would vary depending when the Sheets are created.

In the Sheet Replicator:

A1 - path - WHere the New File needs to be placed
A2 - The Name of the New Excel Sheet
A3 - The No of Sheets to be Moved (In the above case 3 all sheets with "L-V" in the end"
A4 - the Names of the Sheets to be Moved one below the other

Some Changes I made are as follows :

In the Sheet Replicator:

C2- The Name of the New Excel Sheet
C3 - Path - WHere the New File needs to be placed
C4- The No of Sheets to be Moved (In the above case 3 all sheets with "L-V" in the end"
C5 - the Names of the Sheets to be Moved one below the other

..

Want to Copy Sheet Index from the Parent WorkBook as Values with the exact same Format, Column Width Formatting with certain Merged Cells as Values to the New WorkBook as the First Sheet.

Please find the Attached File for reference..

And Thanks a lot for helping out...

Warm Regards
e4excel