Results 1 to 9 of 9

Name worksheet after merge

Threaded View

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    Greenfield, IN
    MS-Off Ver
    Excel 2007
    Posts
    70

    Name worksheet after merge

    I have a code (included below) in which two workbooks are merged into one new workbook with a sheet created for each of the two input sheets.
    I love this code and I need it to name the new workbook "report.xls" as opposed to making it's own ("Book4" or "Book5").
    I do not write code and I know this is simple but every time I try to fix it, I break it.
    (I think it happens somewhere around here:
    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    wkbTemp.Sheets(2).Copy
    Set wkbAll = ActiveWorkbook)


    Here is the code in question.

    Sub Import()
    ' Import Macro
    ' Keyboard Shortcut: Ctrl+Shift+I
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    sDelimiter = "|"
    FilesToOpen = Application.GetOpenFilename _
    (MultiSelect:=True, Title:="Text Files to Open")
    If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
    End If
    x = 1

    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    wkbTemp.Sheets(2).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
    Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=False, _
    Other:=True, OtherChar:="|"
    x = x + 1
    While x <= UBound(FilesToOpen)

    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    With wkbAll
    wkbTemp.Sheets(2).Move After:=.Sheets(.Sheets.Count)
    .Worksheets(x).Columns("A:A").TextToColumns _
    Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=False, _
    Other:=True, OtherChar:=sDelimiter
    End With
    x = x + 1
    Wend

    Sheets.Add Before:=Sheets(1) '-- place at begining
    ActiveSheet.Name = "DIFFERENCES"
    Sheets.Add After:=Sheets(3) '-- place at end
    ActiveSheet.Name = "l_a"

    ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub
    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub
    Last edited by floydian; 06-25-2013 at 03:43 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