Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-11-06
2
Medium Priority
?
574 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 28

Accepted Solution

by:
MacroShadow earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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