Macro Move Sheets

[Packfan86]

Hey everybody,


I have about 80 sheets in a workbook, and I would like to put each sheet into it's own workbook, and have that new workbook called by the value in Column A. Does anybody know how I would do this?

Thanks in advance!

 

[Packfan86]

It worked differently this time! But I get an error.

"Run-time error '1004':
Method 'Copy' of object '_Worksheet' failed

Then I hit debug and it highlights this line

ws.Copy

Any thoughts?

Thanks in advance!

 

[cornflakegirl]

Is your workbook protected?

 

[Packfan86]

I don't think so, but it is saved in a shared drive, does that matter?

 

[cornflakegirl]

Um, it might do. Can you save a test non-shared version, and see if the code works on that?

Can you manually copy a worksheet?

 

[Packfan86]

I tried two things: One I tried to save it as a .xlsm and that didn't work. Then I saved in on my desktop and ran the macro and it gave me the same error.

I can manually copy and paste each sheet into their own new book.

Any thoughts?

Thanks!

 

[cornflakegirl]

Have you got hidden sheets?

 

[DonkeyOte]

At request of CG thought I'd wade in... I have a different approach which may / may not work... no doube less elegant than the one tried thus far.

This code should go in a standard module in your file with 80 sheets etc...

I have assumed file name is set in Cell A2 on each of the 80 sheets... put in a catch in case A2 is emtpy...

Sub lasw10test()
Dim wb1_full As String: wb1_full = ActiveWorkbook.FullName
Dim wb1 As String: wb1 = ActiveWorkbook.Name
Dim wb2 As String
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    wb2 = ws.Cells(2, 1).Value
    If wb2 = "" Then wb2 = ws.Name
    Workbooks.Add
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Replace(wb1_full, wb1, wb2)
    Application.DisplayAlerts = True
    Windows(wb1).Activate
    ws.Copy Before:=Workbooks(wb2).Sheets(1)
    Application.DisplayAlerts = False
    Sheets(2).Delete
    Application.DisplayAlerts = True
    ActiveWorkbook.Close SaveChanges:=True
Next ws
Application.ScreenUpdating = True
End Sub

EDIT: I only delete Sheets(2) as by default my files open with 1 worksheet as opposed to standard 3... if you don't mind leaving the empty sheets in the file you can do away with the delete (and display alerts etc)

CG's points re: protection hold true -- sheets/workbook can't be protected for this approach to work.

 

[Packfan86]

Thank you all! I got it to work. There was a hidden sheet in my book that was causing the error. It works perfectly!

Thanks!

 

[cornflakegirl]

Hey Luke - thanks for popping in!
I've not seen anyone take that approach before - are you idiosyncratic or brilliant?

(3 sheets - who thought that was a good idea?!)

 

[Packfan86]

Since both of you seem so knowledgeable about Excel. I have a question for you about PivotCharts. In 2003 there was a drop down filter box on the actual PivotChart. In 2007 that no longer exists. How do I get it to come back?

Thanks in advance!