Results 1 to 2 of 2

export from 1 workbook to new work book

Threaded View

  1. #1
    Registered User
    Join Date
    02-10-2018
    Location
    toronto ontario
    MS-Off Ver
    office 2010
    Posts
    73

    export from 1 workbook to new work book

    Hi All

    Having so problems with the following vba code

    I need to copy hidden work sheets not all to a new work book and name the new work book as well

    I get a Run time 1004 Cannot rename a sheet to the same name as another sheet
    the code line in debug are ActiveSheet.Name = x(i)

    With .Sheets(x(i))
    any help Please and Thanks


     Sub Results_Click()
    
    
        
            Dim NewName As String, s As String, wb As Workbook, ws As Worksheet, i As Integer, x
        
        s = "Open & Non Pro & Nov Horse & Rookie & Green Horse & Youth & Non Pro & Nrha Green Reiner & Short Stirrups"
        x = Split(s, " & ")
        
        If MsgBox("Sheets:" & vbCr & vbCr & s & vbCr & vbCr & "will be copied to a new workbook" & vbCr & vbCr & _
        "The sheets will be values only (named ranges, formulas and links removed)" & vbCr & vbCr & _
        "Do you want to continue?", vbYesNo, "Create New Workbook") = vbNo Then Exit Sub
        
        NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
    
    
        Application.ScreenUpdating = False
        Workbooks.Add
        Set wb = ActiveWorkbook
        With wb
            For i = 0 To UBound(x)
                Set ws = ThisWorkbook.Sheets(x(i))
                ws.Cells.Copy
                .Sheets.Add after:=Sheets(Sheets.Count):
                ActiveSheet.Name = x(i)
                
                With .Sheets(x(i))
                    .[a1].PasteSpecial Paste:=xlValues
                    .Cells.PasteSpecial Paste:=xlFormats
                    .Cells.Hyperlinks.Delete
                    Application.Goto .[a1]
               
     End With
            
    Next
            Application.DisplayAlerts = False
            For i = 1 To 1
                .Sheets("Sheet" & i).Delete
            Next
            Application.DisplayAlerts = True
            .SaveAs (NewName & ".xls")
        
    End With
        
    ThisWorkbook.Close SaveChanges:=False
        Application.ScreenUpdating = True
    
    End Sub
    Last edited by mmikem; 07-30-2021 at 06:16 PM. Reason: Great it work

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Protected workbook vs share work book
    By Throughstream in forum Excel General
    Replies: 2
    Last Post: 01-26-2017, 06:59 AM
  2. Current work book .xlsm convert to xlsx work book then mail
    By Vcare in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-26-2016, 09:47 AM
  3. VBA Code for populating cells from one Work Book A to another Work Book with condition
    By ray.kanata in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 06-22-2015, 03:46 PM
  4. various worksheet from various work book into one workbook
    By Baskar5353 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-01-2015, 05:12 AM
  5. [SOLVED] Getting specific data from workbook 1 into work book 2
    By jbmcd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2014, 03:01 PM
  6. Replies: 2
    Last Post: 06-20-2012, 05:10 AM
  7. Replies: 9
    Last Post: 01-23-2011, 05:31 PM

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