+ Reply to Thread
Results 1 to 2 of 2

How to copy each sheet into a new workbook respectively?

  1. #1
    Registered User
    Join Date
    01-05-2005
    Posts
    4

    How to copy each sheet into a new workbook respectively?

    I have a file containing many sheets.

    How can I copy each sheets into a new workbook and assign the workbook name same as sheet name?

    Thanks

  2. #2
    Forum Contributor
    Join Date
    11-16-2004
    Posts
    282

    Copy each sheet to a new workbook

    Here's a macro that will copy each sheet in a workbook to a new file and save the file as the worksheet name (be sure to change the file path in BOLD to your own):

    Sub createFilesFromSheets()
    ' Declare variables
    Dim ws As Worksheet, mySheet, myPath
    myPath = "C:\TEMP"
    ' Loop through the worksheets in the workbook & create new file for each sheet
    For Each ws In ActiveWorkbook.Worksheets
    ' Get the worksheet name...
    mySheet = ws.Name
    ' Make a copy of the worksheet...
    ws.Copy Before:=Worksheets(1)
    ' Move worksheet to a new file...
    Worksheets(1).Move
    ' Name new worksheet as its' parent...
    ActiveSheet.Name = mySheet
    ' Save the new file...
    ' Set the file directory to search for previous version...
    ChDir myPath
    ' Delete previous version of the file (if it exists)...
    On Error Resume Next
    Kill myPath & "\" & mySheet & ".xls"
    ' Save as Excel file w/Sheet name
    ActiveWorkbook.SaveAs Filename:=myPath & "\" & mySheet & ".xls", FileFormat:= _
    xlWorkbookNormal, CreateBackup:=False
    ' Close the new file...
    ActiveWindow.Close SaveChanges:=False
    Next ws
    End Sub

    Hope this helps,
    theDude

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1