Difficulties Opening, copying from another workbook and closing


C

Corrie

I have the following script to open another workbook, copy certain
information, going back to the original macro driven workbook and then
closing the file source. It works okay, however it gets hung up for a
long period of time before the file closes. Any ideas what I'm doing
wrong?

Sub ImportUnusual()

Dim WorkbookName As String
Dim FileToOpen As String
Dim WorkbookName1 As String

Sheets("UnU DL").Select
Cells.Select
Selection.Clear
Selection.FormatConditions.Delete
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
WorkbookName = Range("A1").Parent.Parent.Name
MsgBox "Please select the file with the inventory by customer data
you wish to import."
FileToOpen = Application _
.GetOpenFilename("Text Files (*.XLS), *.XLS")
If FileToOpen = "False" Then
End If
Workbooks.Open Filename:=FileToOpen
Range("A1").Select
WorkbookName1 = Range("A1").Parent.Parent.Name
Range("H3").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(WorkbookName).Activate
Windows(WorkbookName1).Close Savechanges:=False
Windows(WorkbookName).Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
End Sub
 
Ad

Advertisements

M

Mike Fogleman

Try this:
Sub ImportUnusual()

Dim WorkbookName As String
Dim FileToOpen As String
Dim WorkbookName1 As String

Sheets("UnU DL").Cells.Clear
WorkbookName = ThisWorkbook.Name
MsgBox "Please select the file with the inventory by customer data you
wish to import."
FileToOpen = Application _
.GetOpenFilename("Text Files (*.XLS), *.XLS")
If FileToOpen = "False" Then
Exit Sub 'if no file selected
End If
Workbooks.Open Filename:=FileToOpen
WorkbookName1 = ActiveWorkbook.Name
Range("H3").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Workbooks(WorkbookName).Worksheets("UnU DL").Range("A1")
Windows(WorkbookName1).Close Savechanges:=False

End Sub

Mike F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top