Results 1 to 2 of 2

Thread: Save each Worksheets as Macro disabled workbooks

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Save each Worksheets as Macro disabled workbooks

    Hey all, i heard this was a well respected forum with good feedback. I need a little guidance...
    I have a master file 'Copy and Save.xlsm'. It contains 3 tabs: FR, GM, and CH.

    I want to save a copy of each tab to a new Excel file and name the file as Tab name - Master file name, e.g. FR - Copy and Save. xls. The master file is a Macro enabled Excel 2003 format and The new file need to be plain Excel 2003-2007 format rather than a Macro enabled format..(It should be ok to change the master file to Macro enabled format but the new files have to be Excel 2003 format...)

    My Macro is able to create a new workbook, save and name it but can't move on to the next tab...

    Code:
    Sub copy_save()
    '
    ' Move and make a copy of each tab from the master file and save as a new workbook. Name it tab name - master file name
    '
    '
    ActiveSheet.Select
    pName = ActiveWorkbook.Path ' the path of the currently active file, the master file
    wbName = ActiveWorkbook.Name ' the file name of the currently active master file
    shtName = ActiveSheet.Name ' the name of the currently selected worksheet, the master file
        
        For i = 1 To Worksheets.Count
        
        ActiveSheet.Select
        ActiveSheet.Copy
        Tabname = pName & "\" & shtName & " - " & wbName 'Name the new workbook as: Tab name - master file name
        
        Dim Newshtname As String
        Newshtname = Tabname
        
        ActiveWorkbook.SaveAs Filename:= _
            Newshtname, FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
            
            
        Sheet(shtName).Activate ' want to Return to master file and move and save the next tab. This doesn't work however.
        
        Range("A1").Select
        
        ActiveSheet.Next.Select 'move to next tab
        
        Next i
        
        End
        
    End Sub
    Last edited by Admin; 01-28-2013 at 07:06 AM.

Similar Threads

  1. Save Worksheets As New File To Specific Folder
    By k0st4din in forum Excel Help
    Replies: 18
    Last Post: 06-08-2013, 04:24 PM
  2. How To Save Macro To Personal Workbook
    By NITIN SHETTY in forum Excel Help
    Replies: 1
    Last Post: 04-07-2013, 01:07 PM
  3. Replies: 1
    Last Post: 03-30-2013, 11:48 PM
  4. View Code is disabled in Excel
    By msiyab in forum Excel Help
    Replies: 2
    Last Post: 02-23-2013, 11:55 AM
  5. Replies: 2
    Last Post: 11-08-2012, 01:15 PM

Tags for this Thread

Posting Permissions

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