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!

 

[cornflakegirl]

Something like:

Sub SaveWS()
Dim wb As Workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs ThisWorkbook.Path & ws.Range("A1").Value & ".xls"
    wb.Close False
Next ws
End Sub

(based on this thread: http://www.mrexcel.com/forum/showthread.php?t=321719)

 

[Packfan86]

Thanks! However, it didn't do anything. It just saved a blank workbook. Any thoughts?

Thanks in advance!

 

[cornflakegirl]

It works for me. Did you adjust the code at all?

 

[Packfan86]

The only thing that I changed was the cell value from "A1" to "A2" Does that make a difference?

 

[cornflakegirl]

Not a jot.

Okay, time for stupid questions.
1. Have you put the code in a standard module in your workbook with 80 sheets?
2. Where is your workbook with 80 sheets saved? Have you thoroughly checked that folder for files that weren't there before?

 

[Packfan86]

Here is the process that I used:

1) I recorded and stopped a new Macro. Then I stepped into that macro and erased and copy and pasted the code that you sent to me.

2) I changed cell "A1" to "A2" in your code in this line:
wb.SaveAs ThisWorkbook.Path & ws.Range("A2").Value & ".xls"

3) I ran the macro. A blank page opened and then closed and was saved in a path that was not where the workbook is located.

4) I just checked the path where the main workbook is located and there was no new files created.

Any thoughts?

Thanks in advance!

 

[cornflakegirl]

Okay - use Alt F11 to open the VBA editor (if it's not already open) and find the code that you pasted in. In the blue bar at the very top of the screen it should say something like "Microsoft Visual Basic - Book1 - [Module1 (Code)]".

What does yours say?

 

[Packfan86]

Mine says: "Microsoft Visual Basic - PERSONAL.XLSB - [Module2 (Code)]"

 

[cornflakegirl]

Okay, that's your problem - the code needs to be in a standard module in your workbook with 80 sheets, not in your personal macro workbook.

This line:
For Each ws In ThisWorkbook.Worksheets

means that the macro works on whichever book the code is in.