Name worksheet after merge

[floydian]

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

[floydian]

The worst part is, I know this is simple to fix and I have been looking at other codes for hours trying to figure out how to do it.

[AB33]

Try,

Please Login or Register  to view this content.

[HaHoBe]

Hi, floydian,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Ciao,
Holger

[AB33]

Holger,
Sorry!
I forgotten to re-mind the OP on code tags.

[floydian]

Apology.
I forgot to use the code tag.

[floydian]

ERROR: Microsoft Excel cannot access the file 'C:\Macrotest\D819D000'.

[AB33]

Yes, it will not access it as it is my own dir. I asked you to change it. Read the comment next to that line.

[floydian]

I removed the line:

Please Login or Register  to view this content.

This allowed the file to be saved in the current directoy.

This worked PERFECTLY!

This inspires me to learn how to write VBA code.

Thank You!

Joe