Results 1 to 3 of 3

Saving 3 Worksheets as New Workbook

Threaded View

  1. #1
    Registered User
    Join Date
    10-12-2015
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2010
    Posts
    29

    Saving 3 Worksheets as New Workbook

    All,

    I had a segment of VBA working some time ago, but I've modified the worksheet so much now that the macro doesn't work like it should. I am trying to create a master file that will automatically refresh my reports, and save each department's information as it's own workbook. Every department will have 3 worksheets, so I would like the VBA to take every 3 worksheets and save them as a new file. I'd like the name of the new file to be "(first worksheet name)_(text in cell B5 on the second worksheet).xls". For example, if my 3 worksheets are 44001, 44001_Month, and 44001_Historical and the text in B5 on 44001_Month were September, then it would save all three of those worksheets with the name 44001_September.xls. This is the code I'm currently using:
    Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Dim xPath As String
    Dim xContents As String
    xPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        For i = 1 To ThisWorkbook.Sheets.Count Step 2
            ThisWorkbook.Sheets(Array(i, i + 1, i + 2)).Copy
            xContents = ActiveWorkbook.Sheets(2).Cells(5, "B").Value
            ActiveWorkbook.SaveAs Filename:=xPath & "\" & ActiveWorkbook.Sheets(1).Name & "_" & xContents & ".xls", FileFormat:=-4143
            Application.ActiveWorkbook.Close False
        Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Any help you can give would be great!

    Thank you.
    Last edited by mmcconkie; 11-05-2015 at 04:34 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Saving worksheets to new workbook
    By jmoore3274 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2014, 10:05 AM
  2. Replies: 5
    Last Post: 08-08-2014, 03:50 PM
  3. saving several worksheets in single workbook in several folders
    By isurunk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2014, 07:09 AM
  4. [SOLVED] password protect worksheets upon opening or saving a workbook.
    By emilyloz in forum Excel General
    Replies: 2
    Last Post: 06-14-2013, 03:14 PM
  5. [SOLVED] macro for saving multiple worksheets in to a new workbook
    By ste1605 in forum Excel General
    Replies: 7
    Last Post: 05-08-2012, 10:05 AM
  6. Worksheets lose formatting when saving workbook as pdf
    By Mosborne1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-03-2011, 12:56 PM
  7. Add-in to saving groups of worksheets as a single workbook
    By l8sk8r in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-11-2008, 07:34 AM

Tags for this Thread

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