Results 1 to 9 of 9

Copy sheets to new file and rename it

Threaded View

  1. #1
    Forum Contributor
    Join Date
    12-19-2006
    Posts
    113

    Copy sheets to new file and rename it

    Hi everybody,
    Without experience in programming I need help from you, everyone who could help me.
    I have 50 worksheets in 1 workbook. Any of those sheets I named XXREC – XX is a number (01REC, 02REC, 03REC,… 50REC). I need to create a Workbook for each worksheet, rename the workbook with the actual name (I mean, number) of the sheet without the 3 last letters (REC) and rename the sheet inside the workbook, with Sheet1.

    Until now, checking other examples, I create this:

    Sub CreateBooks()
        Workbooks.Open Filename:="C:\exp1.xls"
    Dim w As Worksheet
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
            For Each w In ActiveWorkbook.Worksheets
                w.Copy
                    ActiveWorkbook.SaveAs Filename:="C:\Exp\" & w.Name
                        ActiveWorkbook.Close
        Next w
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
                        ActiveWorkbook.Close
    End Sub
    Thank you in advance for any help
    Last edited by VBA Noob; 06-26-2007 at 01:39 PM.

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