Consulting

Results 1 to 2 of 2

Thread: Work Books Sheets copy to Multiple WorkBooks and Rename and Save

  1. #1
    VBAX Regular
    Joined
    Sep 2018
    Posts
    9
    Location

    Smile Work Books Sheets copy to Multiple WorkBooks and Rename and Save

    I have workbook and there many sheets i want to copy one by one sheets to new work book and rename workbook and save except first sheet
    I tried many times, but it saved in one workbook instead of separate workbooks
    Option Explicit
    
    Sub CreateWorkBooks()
        Dim ws AsObject
        Dim i AsLong
        Dim ws_num AsInteger
        Application.ScreenUpdating =False
    
        Set ws = Worksheets
        ws_num = ThisWorkbook.Worksheets.Count
    
        For i =2To ws_num
            'Copy one worksheet as a new workbook
            'The new workbook becomes the ActiveWorkbook
            ws.Copy
    
            'Replace all formulas with values (optional)
            ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
            'May want (not required) to add a file name extension (.xls or .xlsx) to the file name
            ActiveWorkbook.SaveAs ThisWorkbook.Path &"\"& _
              "AR Balance- "& ActiveSheet.Name &" "& Worksheets("DATA Sheet").Range("m2")&".xlsx"
            ActiveWorkbook.Close SaveChanges:=False 
        Next 
    
        Application.ScreenUpdating =True 
    EndSub

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    I'd do something like this

    I don't like to rely on sheet numbers (e.g. 'the 'first sheet') being what I think they are -- safer to use .Name

    Added deleting output workbooks if they already exist


    Option Explicit
    Sub CreateWorkBooks()
        Dim ws As Worksheet
        Dim wb1 As Workbook, wb2 As Workbook
        Dim sFilename As String, sM2 As String
        
        Application.ScreenUpdating = False
        Set wb1 = ThisWorkbook
        sM2 = wb1.Worksheets("DATA").Range("M2")
        
        For Each ws In wb1.Worksheets
            
            If ws.Name = "DATA" Then GoTo GetNext
            
            'Copy one worksheet as a new workbook
            'The new workbook becomes the ActiveWorkbook
            ws.Copy
            
            Set wb2 = ActiveWorkbook
            
            'Replace all formulas with values (optional)
            ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
            'May want (not required) to add a file name extension (.xls or .xlsx) to the file name
            sFilename = wb1.Path & "\" & "AR Balance- " & ActiveSheet.Name & " " & sM2 & ".xlsx"
            
            On Error Resume Next
            Application.DisplayAlerts = False
            Kill sFilename
            Application.DisplayAlerts = True
            On Error GoTo 0
            
            
            wb2.SaveAs sFilename, xlOpenXMLWorkbook
            wb2.Close SaveChanges:=False
            
            wb1.Activate
    GetNext:
        Next
        Application.ScreenUpdating = True
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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
  •