Solved

macro to save each worksheet in the master workbook to new workbook

Posted on 2013-11-06
2
505 Views
Last Modified: 2013-11-07
Hi Expert's
using excel 2003

I need a macro which when run from worksheet "main"..and workbook master and copies and pastes each worksheets into a new workbook deleting sheets 2 & 3..and saves the workbook as an .xls file with the same names as the tab name being copied and lgnores worksheet "main"

the master file is stored at file path c:\my documents\team\mi\exceptions
and new files saved to c:\my documents\team\mi\new
0
Comment
Question by:route217
2 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39627372
Copy this to your master workbook, and run it from there.

Sub SplitWorkbook()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
        If sht.Name <> "main" Then
            sht.Copy
            With ActiveSheet.Cells
                .Copy
                .PasteSpecial Paste:=xlPasteValues
                .PasteSpecial Paste:=xlPasteFormats
            End With
            ActiveWorkbook.SaveAs Filename:="C:\my documents\team\mi\new\" & sht.Name & ".xls"
            ActiveWorkbook.Close savechanges:=False
        End If
    Next sht
End Sub

Open in new window

0
 

Author Comment

by:route217
ID: 39629579
Hi macroshadow

thanks for the feedback and apologise for the delay.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now