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]

I'm not your girl for that one - haven't used 2007 yet. Would suggest you start a new thread for it anyway - then you'll get people who know about Pivot Charts to look at it! (You can link it here if you want.)

One thought - the PDF of MrExcel's latest book is free to download from this site (can't remember exactly where) - it's got a whole big section on Pivot Tables in 2007, so may have the answer to your question.

 

[Packfan86]

Just thought I would ask! Thanks for all your help!

 

[DonkeyOte]

I'm not an 07 user either I'm afraid.

CG - idiosyncratic or brilliant... hmm... inefficient probably best describes me.
(and I didn't say my method was good.. just different...)

 

[cornflakegirl]

Stop putting yourself down
I just want to know why you did it like that, because it wouldn't have occurred to me!

 

[TheBlackDragon7]

Hi guys,

This is very close to a solution I'm looking for...

I have a macro that runs at midnight, and also when the workbook is opened: It checks to see if a worksheet for the month exists (Aug 08), and if it doesn't, it copies a worksheet called "Template", and renames it to the current month and year (tomorrow it will create a worksheet called "Sep 08").

What I would like to happen now, is when a new worksheet is created, it would take last month's worksheet, copy it into another workbook in another directory, and rename the workbook to the name of the sheet that was copied. So tonight at midnight, I want the macro to move "Aug 08" to a new workbook and call that workbook the same name.

Would that be much of a modification to this code?

Thanks in advance,

-Brian

 

[zakasnak]

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)

I really like this code. I've adjusted to this:
%0

 

[zakasnak]

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)

I really like this code. I've adjusted to this:

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("A8").Value & " " & ws.Name & ".xls"
wb.Close False
Next ws
End Sub

... but would like for the new sheets to be saved in the same folder as the original & not have (in my case) "20081230" at the beginning of the file name (the file name should only incl value in A8 and the ws.Name). Can anybody help?

 

[DonkeyOte]

not entirely sure I'm following you're saying file name should be the combination of Range(A8) and worksheet name ?

wb.SaveAs ThisWorkbook.Path & "\" & ws.Range("A8").Value & "_" & ws.Name & ".xls"

 

[zakasnak]

That change is PERFECT. Thank you so much, you just don't realize how much time you've saved me!

 

[zakasnak]

Could I use this to insert today's date?

wb.SaveAs ThisWorkbook.Path & "\" & today() & "_" & ws.Name & ".xls"